Mid function

[vmjan02]

I have this data and trying to get the desired output, but no luck, is there a way to do it. I tried search, mid functions version is excel 2016.

from input to get the required outputs.

Book3
	A	B	C	D	E	F
1	Input	Output 1	Output 2	Output 3	Output 4	Output 5
2	707-00_BYSHJZ00OL0-GPON-1/1/1/6	707-00_BYSHJZ00OL0	1	1	1	6
3	117-00_ESKNRD00OL3-GPON-1/1/13/1	117-00_ESKNRD00OL3	1	1	13	1
4	707-00_BYSHJZ00OL0-GPON-1/1/1/7	707-00_BYSHJZ00OL0	1	1	1	7
5	414-00_KYBRDN00OL1-GPON-1/1/16/1	414-00_KYBRDN00OL1	1	1	16	1
6	241-00_RABGKH00OL1-GPON-1/1/16/2	241-00_RABGKH00OL1	1	1	16	2
7	704-00_KARTJZAAOL0-GPON-1/1/3/4	704-00_KARTJZAAOL0	1	1	3	4
8	704-00_KARTJZAAOL0-GPON-1/1/4/1	704-00_KARTJZAAOL0	1	1	4	1
9	231-00_SHBRKH00OL1-GPON-1/1/1/7	231-00_SHBRKH00OL1	1	1	1	7
10	701-00_KSHARR00OL7-GPON-1/1/8/8	701-00_KSHARR00OL7	1	1	8	8
11	418-00_ESKNDN00OL5-GPON-1/1/11/10	418-00_ESKNDN00OL5	1	1	11	10
12	405-41_QDSYTBAAOL4-GPON-1/1/1/8	405-41_QDSYTBAAOL4	1	1	1	8
13	213-00_MSLMKH00OL2-GPON-1/1/5/16	213-00_MSLMKH00OL2	1	1	5	16
14	212-00_SHRFKH00OL8-GPON-1/1/6/5	212-00_SHRFKH00OL8	1	1	6	5
15	428-00_DEFADN00OL2-GPON-1/1/3/3	428-00_DEFADN00OL2	1	1	3	3
16	232-00_HWAYKH00OL2-GPON-1/1/7/1	232-00_HWAYKH00OL2	1	1	7	1
17	KWRHTBBG_KWRHTBBGOL0-XGS-PON-1/1/16/10	KWRHTBBG_KWRHTBBGOL0	1	1	16	10
18	KWRHTBBG_KWRHTBBGOL0-XGS-PON-1/1/16/11	KWRHTBBG_KWRHTBBGOL0	1	1	16	11
19	KWRHTBBG_KWRHTBBGOL0-XGS-PON-1/1/16/12	KWRHTBBG_KWRHTBBGOL0	1	1	16	12
20	KWRHTBBG_KWRHTBBGOL0-XGS-PON-1/1/16/4	KWRHTBBG_KWRHTBBGOL0	1	1	16	4
21	KWRHTBBG_KWRHTBBGOL0-XGS-PON-1/1/16/5	KWRHTBBG_KWRHTBBGOL0	1	1	16	5
22	KWRHTBBG_KWRHTBBGOL0-XGS-PON-1/1/16/6	KWRHTBBG_KWRHTBBGOL0	1	1	16	6
Sheet1

 

[vmjan02]

=FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE($A2,"-","/"),"/","</m><m>")&"</m></k>","//m[.=number()][position()=last()]")

How about

Fluff.xlsm
	A	B	C	D	E	F
1	Input	Output 1	Output 2	Output 3	Output 4	Output 5
2	707-00_BYSHJZ00OL0-GPON-1/1/1/6	707-00_BYSHJZ00OL0	1	1	1	6
3	117-00_ESKNRD00OL3-GPON-1/1/13/1	117-00_ESKNRD00OL3	1	1	13	1
4	707-00_BYSHJZ00OL0-GPON-1/1/1/7	707-00_BYSHJZ00OL0	1	1	1	7
5	414-00_KYBRDN00OL1-GPON-1/1/16/1	414-00_KYBRDN00OL1	1	1	16	1
6	241-00_RABGKH00OL1-GPON-1/1/16/2	241-00_RABGKH00OL1	1	1	16	2
7	704-00_KARTJZAAOL0-GPON-1/1/3/4	704-00_KARTJZAAOL0	1	1	3	4
8	704-00_KARTJZAAOL0-GPON-1/1/4/1	704-00_KARTJZAAOL0	1	1	4	1
9	231-00_SHBRKH00OL1-GPON-1/1/1/7	231-00_SHBRKH00OL1	1	1	1	7
10	701-00_KSHARR00OL7-GPON-1/1/8/8	701-00_KSHARR00OL7	1	1	8	8
11	418-00_ESKNDN00OL5-GPON-1/1/11/10	418-00_ESKNDN00OL5	1	1	11	10
12	405-41_QDSYTBAAOL4-GPON-1/1/1/8	405-41_QDSYTBAAOL4	1	1	1	8
13	213-00_MSLMKH00OL2-GPON-1/1/5/16	213-00_MSLMKH00OL2	1	1	5	16
14	212-00_SHRFKH00OL8-GPON-1/1/6/5	212-00_SHRFKH00OL8	1	1	6	5
15	428-00_DEFADN00OL2-GPON-1/1/3/3	428-00_DEFADN00OL2	1	1	3	3
16	232-00_HWAYKH00OL2-GPON-1/1/7/1	232-00_HWAYKH00OL2	1	1	7	1
17	KWRHTBBG_KWRHTBBGOL0-XGS-PON-1/1/16/10	KWRHTBBG_KWRHTBBGOL0	1	1	16	10
18	KWRHTBBG_KWRHTBBGOL0-XGS-PON-1/1/16/11	KWRHTBBG_KWRHTBBGOL0	1	1	16	11
19	KWRHTBBG_KWRHTBBGOL0-XGS-PON-1/1/16/12	KWRHTBBG_KWRHTBBGOL0	1	1	16	12
20	KWRHTBBG_KWRHTBBGOL0-XGS-PON-1/1/16/4	KWRHTBBG_KWRHTBBGOL0	1	1	16	4
21	KWRHTBBG_KWRHTBBGOL0-XGS-PON-1/1/16/5	KWRHTBBG_KWRHTBBGOL0	1	1	16	5
22	KWRHTBBG_KWRHTBBGOL0-XGS-PON-1/1/16/6	KWRHTBBG_KWRHTBBGOL0	1	1	16	6
Sheet5

Cell Formulas
Range	Formula
B2:B22	B2	=LEFT(A2,FIND("-",A2,FIND("_",A2))-1)
C2:C22	C2	=FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE($A2,"-","/"),"/","</m><m>")&"</m></k>","//m[.=number()][position()=last()-3]")
D2:D22	D2	=FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE($A2,"-","/"),"/","</m><m>")&"</m></k>","//m[.=number()][position()=last()-2]")
E2:E22	E2	=FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE($A2,"-","/"),"/","</m><m>")&"</m></k>","//m[.=number()][position()=last()-1]")
F2:F22	F2	=FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE($A2,"-","/"),"/","</m><m>")&"</m></k>","//m[.=number()][position()=last()]")

perfect thanks a ton, this is correct

 

[Fluff]

Glad we could help & thanks for the feedback.