If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Extracting data from alpa numeric string
I have about 5,000 alpha numeric records. I need to
derive three separate fields from these individual records. For example: ABC5DE -- ABC 5 DE XYZ77EF -- XYZ 77 EF RST58GFH -- RST 58 GFH Any ideas on how I can do this? Thanks! |
#2
|
|||
|
|||
Extracting data from alpa numeric string
One way assuming that there will be only one set of numericals
start extracting the numbers, with the first value in A1 (replace A1 with your first cell with the data) =MID(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW(INDIRECT( "1:100")),1)),0),100-SUM(1*ISERROR(1*MID(A1,ROW(INDIRECT("1:100")),1))) ) entered with ctrl + shift & enter assume you put that formula in C1, copy down as long as needed, now in B1 put =LEFT(A1,FIND(C1,A1)-1) copy down, finally in D1 put =SUBSTITUTE(SUBSTITUTE(A1,B1,""),C1,"") copy down, select the new range, copy it, paste special as values in place -- Regards, Peo Sjoblom "Adam" wrote in message ... I have about 5,000 alpha numeric records. I need to derive three separate fields from these individual records. For example: ABC5DE -- ABC 5 DE XYZ77EF -- XYZ 77 EF RST58GFH -- RST 58 GFH Any ideas on how I can do this? Thanks! |
#3
|
|||
|
|||
Extracting data from alpa numeric string
"Adam" wrote...
I have about 5,000 alpha numeric records. I need to derive three separate fields from these individual records. For example: ABC5DE -- ABC 5 DE XYZ77EF -- XYZ 77 EF RST58GFH -- RST 58 GFH Assuming you want the results in 3 separate columns, then try these formula for splitting the string in cell A2. B2: [array formula] =LEFT(A2,MATCH(TRUE,ISNUMBER(FIND(MID(A2,Seq,1), "0123456789")),0)-1) C2: [array formula] =MID(A2,LEN(B2)+1,MATCH(TRUE,ISERROR(FIND(MID(A2, Seq+LEN(B2),1),"0123456789")),0)-1) D2: =MID(A2,LEN(B2)+LEN(C2)+1,1024) where the defined name Seq refers to =ROW(INDIRECT("1:1024")) |
Thread Tools | |
Display Modes | |
|
|