Split alpha numeric

alliswell

Board Regular
Joined
Mar 16, 2020
Messages
194
Office Version
  1. 2007
Platform
  1. Windows
  2. Mobile
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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Can you post some sample data using XL2BB?
 
Upvote 0

Attachments

  • Sample.png
    Sample.png
    47.6 KB · Views: 12
Upvote 0
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))),"")
 
Upvote 0
Solution
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
 
Upvote 0
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.
 
Upvote 0
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 ?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top