Break up alpha numeric data

  • Thread starter Thread starter Alex
  • Start date Start date
A

Alex

I have a column with alpha numeric data. How can I break
this one column into separate columns like the following:

3S1W1 ----> 3S 1W 1
3S1W2CD --> 3S 1W 2 CD
2S1W34AA -> 2S 1W 34 AA

The first four digits are constant; always two digits
each. However, the third "new" column may contain
anywhere from one to two digits. The fourth new column
may contain anywhere from zero to two digits.

Thanks!
 
I have a column with alpha numeric data. How can I break
this one column into separate columns like the following:

3S1W1 ----> 3S 1W 1
3S1W2CD --> 3S 1W 2 CD
2S1W34AA -> 2S 1W 34 AA

The first four digits are constant; always two digits
each. However, the third "new" column may contain
anywhere from one to two digits. The fourth new column
may contain anywhere from zero to two digits.

Thanks!
Click to expand...

Assume your data column is A.

The first two formulas are easy:

B1: =LEFT(A1,2)
C1: =MID(A1,3,2)

Then a little more complicated:

D1: =MID(A1,5,ISNUMBER(-MID(A1,6,1))+1)

and E1 is an array formula -- you need to array-enter the formula. That is,
hold down <ctrl><shift> while hitting <enter>. XL will place braces {...}
around the formula:

E1: =MID(A1,SUM(LEN(B1:D1))+1,2)

or, if you don't like array formulas:

E1: =MID(A1,6+ISNUMBER(-MID(A1,6,1)),2)


--ron
 
Alex,

For the first to columns:
Select data range anf go to Data>Text to Column, check
Fixed width in the first screen and click Next.
In the white window click to create two columns wich are 2
characters wide each. click finish.

To split the third column (wich has the remainder data.
Column D, in the next formulas) type the following in the
first cell in column E:
{=MID(D1,MATCH(FALSE,ISERROR(1*MID(D1,ROW(INDIRECT
("1:100")),1)),0),100-SUM(1*ISERROR(1*MID(D1,ROW(INDIRECT
("1:100")),1))))}

I took this formula from a message posted yesterday by Peo
Sjoblom, I don't know how, but it works great! It has to
be array-entered so you have to use ctrl+shift+enter
instead of just enter.

In column F use:
=SUBSTITUTE(D1,E1,"")

Copy down the formulas.

Regards,
Felipe
 
Back
Top