Separating alpha and numeric characters, no delimiter, varying lengths

Originally Posted by Ron Coderre

Left digits

B1: =LEFT(A1,LOOKUP(10^10,INDEX(--LEFT(A1,{1,2,3,4,5,6,7,8,9,10}),0),{1,2,3,4,5,6,7,8,9,10}))

Right digits

D1: =RIGHT(A1,LOOKUP(99^99,--RIGHT($A1,{1,2,3,4,5,6,7,8,9,10}),{1,2,3,4,5,6,7,8,9,10}))

Middle text

C1: =MID(LEFT(A1,LEN(A1)-LEN(D1)),LEN(B1)+1,10)

But if you make the minutest of changes to the first example given by the OP, i.e. if A1 contains 1E105 instead of 1C105 then all these fail.

And that's just one example. They also fail (given the appropriate date/language settings, that is) if A1 contains e.g. 1MAR105, etc., etc.

In B1:

=-LOOKUP(1,-(LEFT(A1,{1,2,3,4,5,6,7,8,9,10})&"**0"))

In D1:

=-LOOKUP(1,-(RIGHT(A1,{1,2,3,4,5,6,7,8,9,10})&"**0"))

should be more rigorous (and a touch simpler).

Regards