How to separate/filter out number with 3 decimal places into another column?

[raimy haidar]

Please help,

currently i'm working on a massive number of data (up to 50 000++). i need to separate or filter out the numbers which have only 3 decimal places into a new column (highlighted in the attached image). is there any specific formula or advanced filter option in order to succeed this task ? Untitled.jpg

[Special-K]

Try using a Helper column with

=IF(LEN(E1)=5,E1,"")

[AlKey]

Formula suggested above will do what you want. However, it will leave blank rows between the extracted numbers. You may also look at an array formula that will extract 3 decimal numbers without leaving blank rows.

=IFERROR(INDEX(A:A,SMALL(IF(LEN($A$1:$A$6)=5,ROW($A$1:$A$6)),ROWS(B$1:B1))),"")

***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER

Row\Col	A	B
1	0.256	0.256
2	0.254	0.254
3	0.0005456	0.356
4	0.1234
5	0.2456
6	0.356

[raimy haidar]

reply to @AlKey

I have tried using the array formula as u stated above :

=IFERROR(INDEX(P:P,SMALL(IF(LEN(P7:P22)=5,ROW(P7:P22)),ROWS(S7))),"")

However, the output shown isnt the same as yours (as shown in attached figure) , where the output data does not filter out the unwanted value:

1 - all numbers which have more than 3 decimal places
2 - numbers which have "0" at the 3rd decimal places ex: 0.510

PROBS 1.jpg

[raimy haidar]

reply to @AlKey

I have tried using the array formula as u stated above :

=IFERROR(INDEX(P:P,SMALL(IF(LEN(P7:P22)=5,ROW(P7:P22)),ROWS(S7))),"")

However, the output shown isnt the same as yours (as shown in attached figure) , where the output data does not filter out the unwanted value:

1 - all numbers which have more than 3 decimal places
2 - numbers which have "0" at the 3rd decimal places ex: 0.510 Attachment 376542

[AlKey]

It didn't work because of changes you made. Here is what if should've been:

=IFERROR(INDEX(P:P,SMALL(IF(LEN(P$7:P$22)=5,ROW(P$7:P$22)),ROWS(S$7:S7))),"")

***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER

[raimy haidar]

that works great sir!

however, there is an issue where it cannot be filtered out the value with "zero" digit (example 0.510, 0.010 "as shown in the attached figure), from my understanding, it detect these values as 2 decimal places. is there any solution for this ?

PROBS2.jpg

[raimy haidar]

and isit possible to filter out together both column A and B (means 0.508 and 0.000147656 is filtered out together )

[newdoverman]

The trailing 0 is only visible due to formatting. If you don't have values that are only two decimal places that you don't want included you could make a minor change to the formula given to extract the values that end in 0. Change the =5 part to <=5 then use formatting to show 3 decimal places.

This ARRAY formula will bring in the neighbouring values for the formula given by AlKey.

Please Login or Register  to view this content.

[raimy haidar]

that works great sir ! thanks you =)