Extracting data from alpa numeric string

  • Thread starter Thread starter Adam
  • Start date Start date
A

Adam

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!
 
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
 
Adam said:
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
Click to expand...

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"))
 
Back
Top