Function to separate numbers w.r.t no. of digits after decimal point

**balasmag** · 08-26-2015, 01:27 AM

Hello Everyone,

Is there a function to segregate (or separate) the numbers w.r.t number of digits after the decimal places?

For ex. lets assume a set of data with 6 data points :
1. 104.1
2. 232.30
3. 101.32
4. 19.1
5. 1.1
6. 200.34

I need a function which will separate the data points with 1 digit after the decimal point as Group A (S.no. 1,4,5 as Group A) and 2 digits after the decimal point as Group B (S.No. 2,3 as Group B)...

Thanks.
Bala

**jayajaya_4** · 08-26-2015, 01:49 AM

Hello and gm

Can I get a bit clarification on what you mean by S.no. 1,4,5 as you are crystal clear with that you want if the data points with 1 digit after the decimal point as Group A and with 2 digits after the decimal point as Group B.

Regards
Jaya

**balasmag** · 08-26-2015, 01:57 AM

Excel 1.pngHello Jaya,

Thanks for the reply.

I would like to segregate the data range - with 1 digit after the decimal point as one group, with 2 digits after the decimal point as another group, and so on..

Hope you got what I meant here..Refer the image..

Thanks
Bala

**Glenn Kennedy** · 08-26-2015, 01:58 AM

there are lots of ways to do this. here's one:
=IFERROR(INDEX($A$1:$A$6,SMALL(IF(LEN($A$1:$A$6)-FIND(".",$A$1:$A$6)=1,ROW($A$1:$A$6)),ROWS($1:1))),"")

entered as an array. To retrun those with 2 dp, change the 1 here: ...$A$6)=1,ROW($A... to a 2

Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

**Glenn Kennedy** · 08-26-2015, 02:00 AM

... or a single formula (array entered), dragged across and down as far as needed

=IFERROR(INDEX($A$1:$A$6,SMALL(IF(LEN($A$1:$A$6)-FIND(".",$A$1:$A$6)=COLUMNS($A:A),ROW($A$1:$A$6)),ROWS($1:1))),"")