Extracting data from alpa numeric string

[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!

 

[Peo Sjoblom]

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

 

[Harlan Grove]

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