Separating alpha and numeric characters, no delimiter, varying lengths

[Trevor K]

This is a follow-up to a previous post that was solved, but I need to add the ability to handle a 2nd alpha string, as in the example below. I have a list of values, represented by column A below. I'd like to help to generate formulas for rows B,C,D,E that will extract each alpha and each numeric portion of the value into separate columns, as in the image below.

Here is the previous post:
http://www.excelforum.com/showthread...t=#post4138955
I'm currently using the solution from XOR LX posted 07-20-2015, 02:48 PM, but it can not handle the 2nd alpha string. Any help would be greatly appreciated.

Note the occasional leading zeros that need to be retained.

Capture.JPG

Thanks,
Trevor

[Trevor K]

I should note that the 2nd alpha string, if present at all, will be either 1 or 2 characters. Total character count could be up to 11.
Thanks

[JeteMc]

This solution uses 11 helper columns which may be hidden for aesthetic purposes. The formula that populates the helper columns, placed in F2; copied to P2 then down, is:

Please Login or Register  to view this content.

The formulas that yield the 1st number, 1st alpha string, 2nd number and 2nd alpha string respectively are:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Let me know if you have any questions.

[Phuocam]

Try this ...

In B2:

=IFERROR(LOOKUP(10^10,--LEFT(A2,ROW($1:$10))),"")

In C2:

=MID(A2,LEN(B2)+1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&1234567890,LEN(B2)+1))-LEN(B2)-1)

In D2:

=IFERROR(LOOKUP(10^10,--MID(A2,LEN(B2)+LEN(C2)+1,ROW($1:$10))),"")

In E2:

=REPLACE(A2,1,LEN(B2)+LEN(C2)+LEN(D2),"")

[Glenn Kennedy]

Or (without helpers), assumes data start in A2):

First number (in B2)
=LOOKUP(99^99,--(0&MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&1234567890)),ROW(INDIRECT("1:"&LEN(A2)+1)))))

First TEXT in C2
=LEFT(SUBSTITUTE(A2,B2,"",1),MIN(FIND({0,1,2,3,4,5,6,7,8,9},SUBSTITUTE(A2,B2,"",1)&"0123456789"))-1)

Second number (in D2, retaining leading zeros - an ARRAY FORMULA):
=LEFT(SUBSTITUTE(A2,B2&C2,"",1),MAX(IF(ISNUMBER(VALUE(MID(SUBSTITUTE(A2,B2&C2,"",1),ROW(INDIRECT("1:" & LEN(SUBSTITUTE(A2,B2&C2,"",1)))),1))),ROW(INDIRECT("1:" & LEN(SUBSTITUTE(A2,B2&C2,"",1)))))))

Second Text (in E2):
=SUBSTITUTE(SUBSTITUTE(A2,B2&C2,"",1),D2,"")

The third one is a bit of a monster!!

Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

[Trevor K]

I'm going with Glenn's most recent post for my particular file, although I think the others would have worked as well. Thank you all for your input.

[Glenn Kennedy]

Glad to have helped and thanks for the Reputation.