Separating alpha and numeric characters, no delimiter, varying lengths

[Trevor K]

This is my first post here. Any help would greatly appreciated. I am trying to separate alpha characters, followed by numeric characters, followed by more alpha characters in 3 separate columns. There are no delimiters, and each set of characters can be different lengths. The alpha characters are always capitalized. I would like to do this with formulas.
Example:
A1=1C105
A2=9MP028
A3=25MJR338
A4=8PV4545

Thank you in advance for your help.
Trevor

[GeneralDisarray]

Would results look like this?

1c105	1	c	105
9mp028	9	mp	028
25mjr338	25	mjr	338
8pv4545	8	pv	4545

[Trevor K]

Yes, except the alpha characters would still be capital letters.

[Ron Coderre]

Using your posted examples and assuming no text will be longer than 10 characters

These formulas, copied down, return the individual segments:

Left digits

Please Login or Register  to view this content.

Right digits

Please Login or Register  to view this content.

Middle text

Please Login or Register  to view this content.

These are the results:
Data Range

	A	B	C	D
1	1C105	1	C	105
2	9MP028	9	MP	028
3	25MJR338	25	MJR	338
4	8PV4545	8	PV	4545

Is that something you can work with?

[Trevor K]

Worked like a charm. This was a huge help to me and I really appreciate it.
Thank you

[Ron Coderre]

Glad you got something you can use!

If that resolves your issue, please take a moment to mark this thread as SOLVED (from the Thread Tools menu)

[XOR LX]

Left digits

Please Login or Register  to view this content.

Right digits

Please Login or Register  to view this content.

Middle text

Please Login or Register  to view this content.

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

[XOR LX]

@Ron Coderre

Apologies. I see the need for your result_vector set-up now to deal with leading zeroes.

In that case:

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

and:

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

Nice idea by the way.

Regards

[Trevor K]

Thanks for the additional help XOR LX. I will mark this as solved.

[Ron Coderre]

I know this has been marked as solved...but, I just thought the solution could be simpler than I was making it.

Consequently, I came up with these formulas:

Please Login or Register  to view this content.

There might be shorter solutions, but I suspect they might be too arcane to readily understand.

[Trevor K]

I have not yet finished my spreadsheet and I will check out your latest solution. Thanks again for your help with this. You probably saved me several hours.