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 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)


"Dot" wrote:

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

B1:
=MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"01234 56789")),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)

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

On Sat, 26 Jul 2008 04:31:00 -0700, Dot wrote:

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

Thanks Joel.. Simple and it works perfectly.

"Joel" wrote:

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)


"Dot" wrote:

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

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&"01234 56789")),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
"Bob Phillips" wrote in message
...
B1:
=MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"01234 56789")),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)

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