Formula to separate text and numbers

  • Thread starter Thread starter Chartreuse
  • Start date Start date
C

Chartreuse

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.
 
Chartreuse said:
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.
Click to expand...

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)
 
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.
 
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! :)
 
Hi, Stephen, I'm 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!
 
Thanks for the help! I knew I should be able to parse items and amounts from
scanned receipts, but I didn't 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.
 
Back
Top