Split alpha numeric

[alliswell]

Hi everybody !
I am using excel 2007
In column A i have some cells having only text and some cells numbers. I want is in column B i must get all text cells in from column A one after other with no empty cells in between and in column C i must get all number cells from column A one after other with no empty cells in between

Thanks n Regards to all

 

[JoeMo]

Can you post some sample data using XL2BB?

 

[alliswell]

Can you post some sample data using XL2BB?

@JoeMo sample attached....

 

[Rick Rothstein]

Here is one way to do it. Use this formula to get the names...

=IFERROR(INDEX(A$1:A$7,SMALL(IF(ISTEXT(A$1:A$7),ROW(A$1:A$7)),ROWS(A$1:A1))),"")

and this formula to get the numbers...

=IFERROR(INDEX(A$1:A$7,SMALL(IF(ISNUMBER(A$1:A$7),ROW(A$1:A$7)),ROWS(A$1:A1))),"")

 

[alliswell]

Here is one way to do it. Use this formula to get the names...

=IFERROR(INDEX(A$1:A$7,SMALL(IF(ISTEXT(A$1:A$7),ROW(A$1:A$7)),ROWS(A$1:A1))),"")

and this formula to get the numbers...

=IFERROR(INDEX(A$1:A$7,SMALL(IF(ISNUMBER(A$1:A$7),ROW(A$1:A$7)),ROWS(A$1:A1))),"")

In both case getting numbers, and when i copy and drag down getting 223 in all cells column B & column C

 

[Rick Rothstein]

I'm a little confused... you marked my post as the Answer but posted it is not working for you. I don't have your version of Excel, so I could not test my formulas, but I think they will (might) work if you array-enter them. To do that, select the first cell you put the formula in, click into the Formula Bar and then press CTRL+SHIFT+ENTER to commit the formula. If that produced a correct value, you can then copy the formula down.

 

[alliswell]

Yea

I'm a little confused... you marked my post as the Answer but posted it is not working for you. I don't have your version of Excel, so I could not test my formulas, but I think they will (might) work if you array-enter them. To do that, select the first cell you put the formula in, click into the Formula Bar and then press CTRL+SHIFT+ENTER to commit the formula. If that produced a correct value, you can then copy the formula down.

Yeah got it sir. Thanks.
Sir just one question, why is small function used in istext function ? Small is used for numbers, to get smallest value right ? So whats its role in istext ? Can you explain please ?

 

[Peter_SSs]

why is small function used in istext function ? Small is used for numbers, to get smallest value right ? So whats its role in istext ? Can you explain please ?

It is looking for the smallest (then 2nd smallest etc) row number that has text in the cell.

 

[alliswell]

It is looking for the smallest (then 2nd smallest etc) row number that has text in the cell.

Thankyou peter and thankyou everybody on mrexcel.
How r u peter ? Hope fine. Met after long time.

 

[Peter_SSs]

Thankyou peter and thankyou everybody on mrexcel.

You're welcome. Glad we could help.

Met after long time.