Text+Number split

[nburaq]

Hi Gents,
I need to split values which are mix of letters and number into a logical notation. first sheet includes only values to be splitted and second sheet is a solution as how it needs to be splitted.
Group 1 always starts with letters ( it might be single letter or double or triple)
Group 2 is 3digits (generally 3 digit number but it might include letters as well like _WI or 5W2)
Group 3 is always 2 digits depending on total character number
Group 4 is always whatever left after group3
I hope there is a solution. Thanks in advance!

empty sheet

Book1
	A	B	C	D	E
1	location	group1	group2	group3	group4
2	Y401
3	Y202A
4	SI_W1
5	L166BA
6	Y5100ET
7	L8010XC
8	Y202D3TT
9	B109A0XS
10	SI_W2_H
11	BF117CC0SC
12	PI050C1_2E
13	SI08511_KL
14	BFA119CD0SC
Sheet2

how it needs to be:

Book1
	A	B	C	D	E
1	location	group1	group2	group3	group4
2	Y401	Y	401
3	Y202A	Y	202	A
4	SI_W1	SI	_W1
5	L166BA	L	166	BA
6	Y5100ET	Y	510	0	ET
7	L8010XC	L	801	0	XC
8	Y202D3TT	Y	202	D3	TT
9	B109A0XS	B	109	A0	XS
10	SI_W2_H	SI	_W2	_H
11	BF117CC0SC	BF	117	CC	0SC
12	PI050C1_2E	PI	050	C1	_2E
13	SI08511_KL	SI	085	11	_KL
14	BFA119CD0SC	BFA	119	CD	0SC
Sheet1

 

[jtakw]

Hi Gents,
I need to split values which are mix of letters and number into a logical notation. first sheet includes only values to be splitted and second sheet is a solution as how it needs to be splitted.
Group 1 always starts with letters ( it might be single letter or double or triple)
Group 2 is 3digits (generally 3 digit number but it might include letters as well like _WI or 5W2)
Group 3 is always 2 digits depending on total character number
Group 4 is always whatever left after group3

Hi,

Can you clarify Bold/Underlined above.
What's the difference between your expected results in Row 6, 7, 10 ???

 

[nburaq]

Hi,

Can you clarify Bold/Underlined above.
What's the difference between your expected results in Row 6, 7, 10 ???

Hi,
Thanks for the help. You can ignore row 10 and for row 6 and 7 after 3 digit number it can start 0ET and 0XC in group 3

 

[jtakw]

Hi,
Thanks for the help. You can ignore row 10 and for row 6 and 7 after 3 digit number it can start 0ET and 0XC in group 3

Does this work for you:

Book3.xlsx
	A	B	C	D	E
1	location	group1	group2	group3	group4
2	Y401	Y	401
3	Y202A	Y	202	A
4	SI_W1	SI	_W1
5	L166BA	L	166	BA
6	Y5100ET	Y	510	0E	T
7	L8010XC	L	801	0X	C
8	Y202D3TT	Y	202	D3	TT
9	B109A0XS	B	109	A0	XS
10	SI_W2_H	SI	_W2	_H
11	BF117CC0SC	BF	117	CC	0SC
12	PI050C1_2E	PI	050	C1	_2E
13	SI08511_KL	SI	085	11	_KL
14	BFA119CD0SC	BFA	119	CD	0SC
Sheet1038

Cell Formulas
Range	Formula
B2:B14	B2	=LEFT(A2,MIN(FIND("_",A2&"_"),MIN(FIND({0,1,2,3,4}+{0;5},A2&1/17)))-1)
C2:C14	C2	=MID(A2,LEN(B2)+1,3)
D2:D14	D2	=MID(A2,LEN(B2)+LEN(C2)+1,2)
E2:E14	E2	=REPLACE(A2,1,LEN(B2)+LEN(C2)+LEN(D2),"")

 

[nburaq]

It works great thanks a lot!

 

[jtakw]

You're welcome, thanks for the feedback.