Text to column

  • Thread starter Thread starter Eileen
  • Start date Start date
E

Eileen

Is there a quick way to separate the below column into
letter and part#(hundreds of rows)?

from:

IC19-004-01
TRS30-000-01
C12-001-01
27-044-01

To:
IC 19-004-01
TRS 30-000-01
C 12-001-01
27-044-01
 
Hi,

Not the most elegant solution I'm afraid, but it appears to work.

if your list starts in A1, then in B1 one enter:

=IF(ISNUMBER(VALUE(MID(A1,1,1))),1,IF(ISNUMBER(VALUE(MID(A1,2,1))),2,IF(ISNUMBER(VALUE(MID(A1,3,1))),3,IF(ISNUMBER(VALUE(MID(A1,4,1))),4))))

in c1 enter

=LEFT(A1,B1-1)

and in d1 enter

=RIGHT(A1,LEN(A1)-B1+1)

Highlight cells b1, c1 and d1 and drag down the page by pulling on the
bottom right hand corner of the selection.

Hope I understood your problem correctly.

Gromit
 
In your sample data, all the parts end with a nine digit number. So, if
the part numbers start in cell A2, enter the following formula in cell
B2: =LEFT(A2,LEN(A2)-9)

Enter the following in C2: =RIGHT(A2,9)

Copy the two formulas down to the last row of data.
 
Back
Top