Text to Columns Letters and Numbers

[Dot]

How do I split a cell with numbers and letters into 2 separate cells. I have
tried the text to columns wizard and can't figure it out. Here is an example
of what I am trying to split. There are no spaces between the numbers and
letters.
A1: 9A
A2: 9B
A3: 19A
A4: 19C

 

[Joel]

You have to add two auxilary columns with this formula in each column. The
copy formula down new columns. These formulkas only work when you have a
fixed number of characters (in this case one character) at the end of the
string.

=LEFT(A1,LEN(A1)-1)
=RIGHT(A1,1)

 

[Bob Phillips]

B1:
=MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},""))))
C1: =SUBSTITUTE(A1,B1,"")

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Ron Rosenfeld]

How do I split a cell with numbers and letters into 2 separate cells. I have
tried the text to columns wizard and can't figure it out. Here is an example
of what I am trying to split. There are no spaces between the numbers and
letters.
A1: 9A
A2: 9B
A3: 19A
A4: 19C

You don't write exactly what you want. Do you want to have the numbers in one
cell and the letter in the other?

If so, then try these formulas:

B1:
=LOOKUP(9.9E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"
0123456789")),ROW(INDIRECT("1:"&LEN(A1)))))

C1: =SUBSTITUTE(A1,B1,"",1)

and fill down as far as necessary.

If you then want to eliminate the formulas and column A, you could

select B1:Cn
Edit/Copy
Edit/Paste Special/Values

Then delete column A
--ron

 

[Dot]

Thanks Joel.. Simple and it works perfectly.

You have to add two auxilary columns with this formula in each column. The
copy formula down new columns. These formulkas only work when you have a
fixed number of characters (in this case one character) at the end of the
string.

=LEFT(A1,LEN(A1)-1)
=RIGHT(A1,1)

 

[FLKulchar]

Attn: BOB PHILLIPS:

How did you ever have the wherewithal to come up with your formula:

=MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},""))))

which separates the "number" portion of the data (such as 'splitting' 19a
into 19??????

Please advise! I am fully aware that it works perfectly, but without your
input, I NEVER could have done this on my own!!

Thanks,

FLKulchar