Text to Columns Letters and Numbers

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dot - 26 Jul 2008 12:31 GMT

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

Reply to this Message

Joel - 26 Jul 2008 12:48 GMT

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)

> 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
[quoted text clipped - 4 lines]
> A3: 19A
> A4: 19C

Reply to this Message

Dot - 26 Jul 2008 14:38 GMT

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
[quoted text clipped - 12 lines]
> > A3: 19A
> > A4: 19C

Reply to this Message

Bob Phillips - 26 Jul 2008 12:52 GMT

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

Signature

HTH

Bob

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

> How do I split a cell with numbers and letters into 2 separate cells. I
> have
[quoted text clipped - 6 lines]
> A3: 19A
> A4: 19C

Reply to this Message

FLKulchar - 27 Jul 2008 12:27 GMT

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

> 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},""))))
[quoted text clipped - 11 lines]
>> A3: 19A
>> A4: 19C

Reply to this Message

Ron Rosenfeld - 26 Jul 2008 13:03 GMT

>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
[quoted text clipped - 4 lines]
>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

Reply to this Message