Excel - Formula to separate text and numbers

Asked By Chartreus on 06-Dec-07 04:52 AM
Hi, I have a spreadsheet with many rows of data containing a combination of
letters and numbers.  I would like to be able to use formulas to separate the
text from the numbers and end up with the letters in one cell and the numbers
in another, so that the following:

AB1
A20
TMH255

Would become:

AB            1
A              20
TMH         255

Thank you in advance for any help.




Stephen replied on 06-Dec-07 05:04 AM
Look here for a formula that will find the position of the first digit (0-9)
in a string:
http://www.cpearson.com/excel/stringformulas.aspx

You could use this (say in B1) for data in A1, and then use these formulas
to get the letters and numbers respectively:
=LEFT(A1,LEN(A1)-B1)
=RIGHT(A1,LEN(A1)-B1+1)
Chartreus replied on 06-Dec-07 05:27 AM
Hi, Stephen, thank you very much for your response.  I clicked on the link
and got the formula you mentioned and pasted it in along with the two
formulas you gave me.  The "RIGHT" formula worked great, but I didn't quite
get the correct result using the "LEFT" formula.

Here's the result I got:

1
A	20
TM	255
D1	100
XYZ	1000

I've tried changing the LEFT formula in various ways but I haven't hit upon
the correct syntax yet.  Do you know how I might be able to change it to make
it work?

Thanks in advance.
Chartreus replied on 06-Dec-07 05:43 AM
Hi, actually, I think I was able to get it.  I changed the formula in column
C to:

=LEFT(A1,LEN(A1)-LEN(D1))

I have the result of the RIGHT formula in column D so subtracting the LEN of
that seems to leave me with only the letters in column C.

Thank you very much for your help! :)
Stephen replied on 06-Dec-07 05:47 AM
My apologies - I posted completely the wrong formula! Try this:
=LEFT(A1,B1-1)
Chartreus replied on 11-Dec-07 02:30 AM
Hi, Stephen, I am sorry to respond so late.  I just wanted to say thank you
for the new LEFT formula. :)  I tried it and it works great. :)  Thanks for
all your help!
Pmmm replied on 20-Oct-09 06:28 PM
Thanks for the help!  I knew I should be able to parse items and amounts from
scanned receipts, but I did not know exactly how.  When I saw the LEFT program
error that I also got, I knew I was on the right track! :)  Took some
tweaking of course for my particular use, but I had fun.