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

 

[alansidman]

With Power Query (aka Get and Transform Data found on the Data Tab)

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Input", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, true), {"Input.1", "Input.2"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Input.2", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Input.2.1", "Input.2.2", "Input.2.3", "Input.2.4"})
in
    #"Split Column by Delimiter1"

 

[vmjan02]

ok, but i am looking for excel formula

 

[Joe4]

If that really is representative of all your data, try this formula in cell B2 and copy down:

=IF(ISNUMBER(FIND("-GPON",A2)),LEFT(A2,FIND("-GPON",A2)-1),LEFT(A2,FIND("-XGS",A2)-1))

It works exactly correctly for every single example you posted.

 

[vmjan02]

If that really is representative of all your data, try this formula in cell B2 and copy down:

=IF(ISNUMBER(FIND("-GPON",A2)),LEFT(A2,FIND("-GPON",A2)-1),LEFT(A2,FIND("-XGS",A2)-1))

It works exactly correctly for every single example you posted.

thanks this is correct and perfect, this is for output1, but how about the rest of the outputs 2 to 5

 

[vmjan02]

I tried it but outputs 2 to 5 are not working for me, any way out.

Book1
	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	#VALUE!	#VALUE!	#VALUE!	#VALUE!
3	117-00_ESKNRD00OL3-GPON-1/1/13/1	117-00_ESKNRD00OL3	#VALUE!	#VALUE!	#VALUE!	#VALUE!
4	707-00_BYSHJZ00OL0-GPON-1/1/1/7	707-00_BYSHJZ00OL0	#VALUE!	#VALUE!	#VALUE!	#VALUE!
5	414-00_KYBRDN00OL1-GPON-1/1/16/1	414-00_KYBRDN00OL1	#VALUE!	#VALUE!	#VALUE!	#VALUE!
6	241-00_RABGKH00OL1-GPON-1/1/16/2	241-00_RABGKH00OL1	#VALUE!	#VALUE!	#VALUE!	#VALUE!
7	704-00_KARTJZAAOL0-GPON-1/1/3/4	704-00_KARTJZAAOL0	#VALUE!	#VALUE!	#VALUE!	#VALUE!
8	704-00_KARTJZAAOL0-GPON-1/1/4/1	704-00_KARTJZAAOL0	#VALUE!	#VALUE!	#VALUE!	#VALUE!
9	231-00_SHBRKH00OL1-GPON-1/1/1/7	231-00_SHBRKH00OL1	#VALUE!	#VALUE!	#VALUE!	#VALUE!
10	701-00_KSHARR00OL7-GPON-1/1/8/8	701-00_KSHARR00OL7	#VALUE!	#VALUE!	#VALUE!	#VALUE!
11	418-00_ESKNDN00OL5-GPON-1/1/11/10	418-00_ESKNDN00OL5	#VALUE!	#VALUE!	#VALUE!	#VALUE!
12	405-41_QDSYTBAAOL4-GPON-1/1/1/8	405-41_QDSYTBAAOL4	#VALUE!	#VALUE!	#VALUE!	#VALUE!
13	213-00_MSLMKH00OL2-GPON-1/1/5/16	213-00_MSLMKH00OL2	#VALUE!	#VALUE!	#VALUE!	#VALUE!
14	212-00_SHRFKH00OL8-GPON-1/1/6/5	212-00_SHRFKH00OL8	#VALUE!	#VALUE!	#VALUE!	#VALUE!
15	428-00_DEFADN00OL2-GPON-1/1/3/3	428-00_DEFADN00OL2	#VALUE!	#VALUE!	#VALUE!	#VALUE!
16	232-00_HWAYKH00OL2-GPON-1/1/7/1	232-00_HWAYKH00OL2	#VALUE!	#VALUE!	#VALUE!	#VALUE!
17	KWRHTBBG_KWRHTBBGOL0-XGS-PON-1/1/16/10	KWRHTBBG_KWRHTBBGOL0	#VALUE!	#VALUE!	#VALUE!	#VALUE!
18	KWRHTBBG_KWRHTBBGOL0-XGS-PON-1/1/16/11	KWRHTBBG_KWRHTBBGOL0	#VALUE!	#VALUE!	#VALUE!	#VALUE!
19	KWRHTBBG_KWRHTBBGOL0-XGS-PON-1/1/16/12	KWRHTBBG_KWRHTBBGOL0	#VALUE!	#VALUE!	#VALUE!	#VALUE!
20	KWRHTBBG_KWRHTBBGOL0-XGS-PON-1/1/16/4	KWRHTBBG_KWRHTBBGOL0	#VALUE!	#VALUE!	#VALUE!	#VALUE!
21	KWRHTBBG_KWRHTBBGOL0-XGS-PON-1/1/16/5	KWRHTBBG_KWRHTBBGOL0	#VALUE!	#VALUE!	#VALUE!	#VALUE!
22	KWRHTBBG_KWRHTBBGOL0-XGS-PON-1/1/16/6	KWRHTBBG_KWRHTBBGOL0	#VALUE!	#VALUE!	#VALUE!	#VALUE!
Sheet1

Cell Formulas
Range	Formula
B2:B22	B2	=IF(ISNUMBER(FIND("-GPON",A2)),LEFT(A2,FIND("-GPON",A2)-1),LEFT(A2,FIND("-XGS",A2)-1))
C2:C22	C2	=MID(A2,FIND("_",A2)+1,FIND("_",A2,FIND("_",A2)+1)-FIND("_",A2)-1)
D2:D22	D2	=MID(A2,FIND("_",A2)+1,FIND("_",A2,FIND("_",A2)+1)-FIND("_",A2)-1)
E2:E22	E2	=MID(A2,FIND("_",A2,FIND("_",A2)+1)+1,FIND("_",A2,FIND("_",A2,FIND("_",A2)+1)+1)-FIND("_",A2,FIND("_",A2)+1)-1)
F2:F22	F2	=MID(A2,FIND("/",A2,FIND("/",A2,FIND("/",A2,FIND("/",A2)+1)+1)+1)+1,LEN(A2))

 

[Joe4]

This would be SO much easier if you were using Excel 365 or Excel 2021, where you had access to the LET function.
That would prevent the need for having to calculate/check the same value multiple times in the same formula.

 

[Joe4]

You could use this methodology here to get everything after the last "-" in a "helper" cell to get the last values that you then need to delimit by commas.

Find Position of the Last Occurrence of a Character in Excel

In this tutorial, you will learn how to find the position of the last occurrence of a character in a String in Excel (step by step explanation)

trumpexcel.com

Then, it is just a matter of splitting that piece up.
In Excel 365, it is really easy with the TEXTSPLIT function.
Otherwise, you could hard-code it and use "Text to Columns", or use a combination of MID and other string functions.

 

[Fluff]

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()]")

 

[vmjan02]

thanks but need excel formula please,