Tuesday, December 15, 2009

How can I seperate text and numbers in excel?

I have, in column A, a list of people's names and id numbers; like this:


12345


John


Smith


67890


John


Smyth





And I would like to move all the id numbers to column B, but keep them with the correct name as much as possible.


Any suggestions?How can I seperate text and numbers in excel?
in B1, make this formula:


=IF(VALUE(A1)%26gt;0,A2%26amp;'; ';%26amp;A3,';';)





drag this all the way to the bottom





copy column B and paste-special values (to get rid of the formulas)





sort by column B and delete anything that says ';#VALUE!';





Now you have the number in column A and the name in columb BHow can I seperate text and numbers in excel?
make B1 formula:





= if(istext(A1), A1, ';';)





Drag that down the column, and the 1 will become 2, 3, etc.





.

No comments:

Post a Comment