vba code or formula to Split string when alphabetic changes to numeric

[jsb1921]

I have some alphanumeric data in col A. it has 4 parts.
part 1 starts at char 1 and ends whereever a numeric char is found.
part 2 ends when a alphabetic char is found.
part 3 is only one char always.
part 4 is rest of the string. can be a number or a decimal.

I am looking for a vba code (or excel formula) to split them in the 4 parts in a quick way
thanks in advance

Book1
	A	B	C	D	E
1	TREE200717C1550	TREE	200717	C	1550
2	ZAN200626C54	ZAN	200626	C	54
3	ZAN200717C52.5	ZAN	200717	C	52.5
4	BHC200619C19.5	BHC	200619	C	19.5
5	C200619C13.5	C	200619	C	13.5
6	GPS200717C12	GPS	200717	C	12
7	MAXR200821C20	MAXR	200821	C	20
8
9
10	ALPHANUMERIC+NUMERIC+ALPHANUMERIC+NUMERIC OR DECIMAL
Sheet1

 

[kevin9999]

I really hope someone can come along with a better solution than this (VBA using Regex perhaps?)

split.xlsm
	A	B	C	D	E
1	TREE200717C1550	TREE	200717	C	1550
2	ZAN200626C54	ZAN	200626	C	54
3	ZAN200717C52.5	ZAN	200717	C	52.5
4	BHC200619C19.5	BHC	200619	C	19.5
5	C200619C13.5	C	200619	C	13.5
6	GPS200717C12	GPS	200717	C	12
7	MAXR200821C20	MAXR	200821	C	20
8
9
10	ALPHANUMERIC+NUMERIC+ALPHANUMERIC+NUMERIC OR DECIMAL
Sheet1

Cell Formulas
Range	Formula
B1:B7	B1	=TRIM(LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1))
C1:C7	C1	=LOOKUP(10^6,1*MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789",FIND(B1," "&A1))),{2,3,4,5,6}))
D1:D7	D1	=MID(A1,LOOKUP(2,1/(ISNUMBER(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"abcdefghijklmnopqrstuvwxyz"))),ROW(INDIRECT("1:"&LEN(A1)))),1)
E1:E7	E1	=RIGHT(A1,LEN(A1)-SEARCH("#",SUBSTITUTE(A1,D1,"#",LEN(A1)-LEN(SUBSTITUTE(A1,D1,"")))))

 

[jsb1921]

kevin9999, Simple and does the trick. thanks a lot.

 

[kevin9999]

Happy to help, and thanks for the feedback

 

[Peter_SSs]

Perhaps your sample data is not fully representative but for all of your examples, the second part is a 6-digit number beginning with 2.
If that was so, then

22 06 30.xlsm
	A	B	C	D	E
1	TREE200717C1550	TREE	200717	C	1550
2	ZAN200626C54	ZAN	200626	C	54
3	ZAN200717C52.5	ZAN	200717	C	52.5
4	BHC200619C19.5	BHC	200619	C	19.5
5	C200619C13.5	C	200619	C	13.5
6	GPS200717C12	GPS	200717	C	12
7	MAXR200821C20	MAXR	200821	C	20
Split Text

Cell Formulas
Range	Formula
B1:B7	B1	=LEFT(A1,FIND(C1,A1)-1)
C1:C7	C1	=MID(A1,FIND(2,A1),6)+0
D1:D7	D1	=MID(A1,FIND(C1,A1)+6,1)
E1:E7	E1	=REPLACE(A1,1,LEN(B1&C1&D1),"")+0