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.



"Stephen" wrote:

"Chartreuse" wrote in message
...
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.

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)