separate string into letters and numbers
 

I have strings of data as such:
SYG0919L625
SPZ1020X1025
DO0919M25.5

I need to separate these strings into 4 columns delimited by number and
letter. e.g.
The data should look as such:

Col 1 Col 2 Col 3 Col 4
SYG 0919 L 625
SPZ 1020 X 1025
DO 0919 M 25.5

Any ideas?

separate string into letters and numbers
 

If your strings of data are in cells (Column A assumed below), then try
these formula...

Column B
----------------
=LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789"))-1)

Column C
----------------
=TEXT(LOOKUP(9.9E+307,--LEFT(SUBSTITUTE(A1,B1,""),ROW($1:$99))),"0000")

Column D
----------------
=SUBSTITUTE(SUBSTITUTE(A1,B1&C1,""),E1,"")

Column E
----------------
=LOOKUP(9.9E+307,--RIGHT(A1,ROW($1:$99)))

--
Rick (MVP - Excel)


"aileen" wrote in message
...
I have strings of data as such:
SYG0919L625
SPZ1020X1025
DO0919M25.5

I need to separate these strings into 4 columns delimited by number and
letter. e.g.
The data should look as such:

Col 1 Col 2 Col 3 Col 4
SYG 0919 L 625
SPZ 1020 X 1025
DO 0919 M 25.5

Any ideas?

separate string into letters and numbers
 

Assuming your initial string is in column A, and that all data takes on the
structure of your examples:
2 or 3 alpha characters, followed by
4 numeric characters, followed by
1 alpha character, followed by
anything else

These formulas should work for you:
Assume first entry is on row 2 (in cell A2)
in B2 put this formula:
=IF(ISNUMBER(VALUE(MID(A2,3,1))),LEFT(A2,2),LEFT(A 2,3))
in C2 put this formula
=MID(A2,LEN(B2)+1,4)
in D2 put this formula
=MID(A2,LEN(B2)+LEN(C2)+1,1)
and finally, in E2, put this formula
=RIGHT(A2,LEN(A2)-(LEN(B2)+LEN(C2)+LEN(D2)))

fill the formulas down the sheet as required.

Hope this helps you with the problem. If your data takes on other formats,
then we have to do more work.

"aileen" wrote:

I have strings of data as such:
SYG0919L625
SPZ1020X1025
DO0919M25.5

I need to separate these strings into 4 columns delimited by number and
letter. e.g.
The data should look as such:

Col 1 Col 2 Col 3 Col 4
SYG 0919 L 625
SPZ 1020 X 1025
DO 0919 M 25.5

Any ideas?

separate string into letters and numbers
 

I like JLatham's formula for Column C better than the one I posted, so I
would suggest you use it instead of what I posted (change his A2 to A1 to
match the rest of my formulas assumed starting cell though). As for the rest
of your data strings, the formulas I posted originally work and are
independent of the length of each part of your text... that is, the leading
text can be any number of characters long, the first "number" is assumed to
always be 4-digits long, the next text can be any number of characters long
(not just the one character you show) and the last number can be any length.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
If your strings of data are in cells (Column A assumed below), then try
these formula...

Column B
----------------
=LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789"))-1)

Column C
----------------
=TEXT(LOOKUP(9.9E+307,--LEFT(SUBSTITUTE(A1,B1,""),ROW($1:$99))),"0000")

Column D
----------------
=SUBSTITUTE(SUBSTITUTE(A1,B1&C1,""),E1,"")

Column E
----------------
=LOOKUP(9.9E+307,--RIGHT(A1,ROW($1:$99)))

--
Rick (MVP - Excel)


"aileen" wrote in message
...
I have strings of data as such:
SYG0919L625
SPZ1020X1025
DO0919M25.5

I need to separate these strings into 4 columns delimited by number and
letter. e.g.
The data should look as such:

Col 1 Col 2 Col 3 Col 4
SYG 0919 L 625
SPZ 1020 X 1025
DO 0919 M 25.5

Any ideas?

separate string into letters and numbers
 

Worked perfectly, thanks.

"JLatham" wrote:

Assuming your initial string is in column A, and that all data takes on the
structure of your examples:
2 or 3 alpha characters, followed by
4 numeric characters, followed by
1 alpha character, followed by
anything else

These formulas should work for you:
Assume first entry is on row 2 (in cell A2)
in B2 put this formula:
=IF(ISNUMBER(VALUE(MID(A2,3,1))),LEFT(A2,2),LEFT(A 2,3))
in C2 put this formula
=MID(A2,LEN(B2)+1,4)
in D2 put this formula
=MID(A2,LEN(B2)+LEN(C2)+1,1)
and finally, in E2, put this formula
=RIGHT(A2,LEN(A2)-(LEN(B2)+LEN(C2)+LEN(D2)))

fill the formulas down the sheet as required.

Hope this helps you with the problem. If your data takes on other formats,
then we have to do more work.

"aileen" wrote:

I have strings of data as such:
SYG0919L625
SPZ1020X1025
DO0919M25.5

I need to separate these strings into 4 columns delimited by number and
letter. e.g.
The data should look as such:

Col 1 Col 2 Col 3 Col 4
SYG 0919 L 625
SPZ 1020 X 1025
DO 0919 M 25.5

Any ideas?

separate string into letters and numbers
 

Thanks so much. This covered even more scenarios for me.

"Rick Rothstein" wrote:

If your strings of data are in cells (Column A assumed below), then try
these formula...

Column B
----------------
=LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789"))-1)

Column C
----------------
=TEXT(LOOKUP(9.9E+307,--LEFT(SUBSTITUTE(A1,B1,""),ROW($1:$99))),"0000")

Column D
----------------
=SUBSTITUTE(SUBSTITUTE(A1,B1&C1,""),E1,"")

Column E
----------------
=LOOKUP(9.9E+307,--RIGHT(A1,ROW($1:$99)))

--
Rick (MVP - Excel)


"aileen" wrote in message
...
I have strings of data as such:
SYG0919L625
SPZ1020X1025
DO0919M25.5

I need to separate these strings into 4 columns delimited by number and
letter. e.g.
The data should look as such:

Col 1 Col 2 Col 3 Col 4
SYG 0919 L 625
SPZ 1020 X 1025
DO 0919 M 25.5

Any ideas?

.

separate string into letters and numbers
 

You've discovered what I was going to say: Rick's formulas are more 'generic'
and will cover more general cases, where the ones I provided were tailored to
your data. Not that Rick likes the one I put up for column C better than
his, so you may have a combined 'combined general/specific' solution by using
most of his and one of mine <g


"aileen" wrote:

Thanks so much. This covered even more scenarios for me.

"Rick Rothstein" wrote:

If your strings of data are in cells (Column A assumed below), then try
these formula...

Column B
----------------
=LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789"))-1)

Column C
----------------
=TEXT(LOOKUP(9.9E+307,--LEFT(SUBSTITUTE(A1,B1,""),ROW($1:$99))),"0000")

Column D
----------------
=SUBSTITUTE(SUBSTITUTE(A1,B1&C1,""),E1,"")

Column E
----------------
=LOOKUP(9.9E+307,--RIGHT(A1,ROW($1:$99)))

--
Rick (MVP - Excel)


"aileen" wrote in message
...
I have strings of data as such:
SYG0919L625
SPZ1020X1025
DO0919M25.5

I need to separate these strings into 4 columns delimited by number and
letter. e.g.
The data should look as such:

Col 1 Col 2 Col 3 Col 4
SYG 0919 L 625
SPZ 1020 X 1025
DO 0919 M 25.5

Any ideas?

.