| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Extracting numbers from a string containg letters at the end | Excel Programming | |||
| Extracting numbers and letters in a single cell to separate cells | Excel Discussion (Misc queries) | |||
| separate numbers out of string | Excel Worksheet Functions | |||
| Converting Text String to Separate Numbers | Excel Discussion (Misc queries) | |||
| Splitting string into letters and numbers | Excel Programming | |||
Hybrid Mode
