Mid function

V

vmjan02

Well-known Member
Joined
Aug 15, 2012
Messages
1,134
Office Version
  1. 365
  2. 2021
  3. 2019
  4. 2016
  5. 2013
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
ABCDEF
1InputOutput 1Output 2Output 3Output 4Output 5
2707-00_BYSHJZ00OL0-GPON-1/1/1/6707-00_BYSHJZ00OL01116
3117-00_ESKNRD00OL3-GPON-1/1/13/1117-00_ESKNRD00OL311131
4707-00_BYSHJZ00OL0-GPON-1/1/1/7707-00_BYSHJZ00OL01117
5414-00_KYBRDN00OL1-GPON-1/1/16/1414-00_KYBRDN00OL111161
6241-00_RABGKH00OL1-GPON-1/1/16/2241-00_RABGKH00OL111162
7704-00_KARTJZAAOL0-GPON-1/1/3/4704-00_KARTJZAAOL01134
8704-00_KARTJZAAOL0-GPON-1/1/4/1704-00_KARTJZAAOL01141
9231-00_SHBRKH00OL1-GPON-1/1/1/7231-00_SHBRKH00OL11117
10701-00_KSHARR00OL7-GPON-1/1/8/8701-00_KSHARR00OL71188
11418-00_ESKNDN00OL5-GPON-1/1/11/10418-00_ESKNDN00OL5111110
12405-41_QDSYTBAAOL4-GPON-1/1/1/8405-41_QDSYTBAAOL41118
13213-00_MSLMKH00OL2-GPON-1/1/5/16213-00_MSLMKH00OL211516
14212-00_SHRFKH00OL8-GPON-1/1/6/5212-00_SHRFKH00OL81165
15428-00_DEFADN00OL2-GPON-1/1/3/3428-00_DEFADN00OL21133
16232-00_HWAYKH00OL2-GPON-1/1/7/1232-00_HWAYKH00OL21171
17KWRHTBBG_KWRHTBBGOL0-XGS-PON-1/1/16/10KWRHTBBG_KWRHTBBGOL0111610
18KWRHTBBG_KWRHTBBGOL0-XGS-PON-1/1/16/11KWRHTBBG_KWRHTBBGOL0111611
19KWRHTBBG_KWRHTBBGOL0-XGS-PON-1/1/16/12KWRHTBBG_KWRHTBBGOL0111612
20KWRHTBBG_KWRHTBBGOL0-XGS-PON-1/1/16/4KWRHTBBG_KWRHTBBGOL011164
21KWRHTBBG_KWRHTBBGOL0-XGS-PON-1/1/16/5KWRHTBBG_KWRHTBBGOL011165
22KWRHTBBG_KWRHTBBGOL0-XGS-PON-1/1/16/6KWRHTBBG_KWRHTBBGOL011166
Sheet1
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
With Power Query (aka Get and Transform Data found on the Data Tab)

Power Query: 
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"
 
Upvote 0
If that really is representative of all your data, try this formula in cell B2 and copy down:
Excel Formula: 
=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.
 
Upvote 0
Joe4 said:
If that really is representative of all your data, try this formula in cell B2 and copy down:
Excel Formula: 
=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.
Click to expand...
thanks this is correct and perfect, this is for output1, but how about the rest of the outputs 2 to 5
 
Upvote 0
I tried it but outputs 2 to 5 are not working for me, any way out.


Book1
ABCDEF
1InputOutput 1Output 2Output 3Output 4Output 5
2707-00_BYSHJZ00OL0-GPON-1/1/1/6707-00_BYSHJZ00OL0#VALUE!#VALUE!#VALUE!#VALUE!
3117-00_ESKNRD00OL3-GPON-1/1/13/1117-00_ESKNRD00OL3#VALUE!#VALUE!#VALUE!#VALUE!
4707-00_BYSHJZ00OL0-GPON-1/1/1/7707-00_BYSHJZ00OL0#VALUE!#VALUE!#VALUE!#VALUE!
5414-00_KYBRDN00OL1-GPON-1/1/16/1414-00_KYBRDN00OL1#VALUE!#VALUE!#VALUE!#VALUE!
6241-00_RABGKH00OL1-GPON-1/1/16/2241-00_RABGKH00OL1#VALUE!#VALUE!#VALUE!#VALUE!
7704-00_KARTJZAAOL0-GPON-1/1/3/4704-00_KARTJZAAOL0#VALUE!#VALUE!#VALUE!#VALUE!
8704-00_KARTJZAAOL0-GPON-1/1/4/1704-00_KARTJZAAOL0#VALUE!#VALUE!#VALUE!#VALUE!
9231-00_SHBRKH00OL1-GPON-1/1/1/7231-00_SHBRKH00OL1#VALUE!#VALUE!#VALUE!#VALUE!
10701-00_KSHARR00OL7-GPON-1/1/8/8701-00_KSHARR00OL7#VALUE!#VALUE!#VALUE!#VALUE!
11418-00_ESKNDN00OL5-GPON-1/1/11/10418-00_ESKNDN00OL5#VALUE!#VALUE!#VALUE!#VALUE!
12405-41_QDSYTBAAOL4-GPON-1/1/1/8405-41_QDSYTBAAOL4#VALUE!#VALUE!#VALUE!#VALUE!
13213-00_MSLMKH00OL2-GPON-1/1/5/16213-00_MSLMKH00OL2#VALUE!#VALUE!#VALUE!#VALUE!
14212-00_SHRFKH00OL8-GPON-1/1/6/5212-00_SHRFKH00OL8#VALUE!#VALUE!#VALUE!#VALUE!
15428-00_DEFADN00OL2-GPON-1/1/3/3428-00_DEFADN00OL2#VALUE!#VALUE!#VALUE!#VALUE!
16232-00_HWAYKH00OL2-GPON-1/1/7/1232-00_HWAYKH00OL2#VALUE!#VALUE!#VALUE!#VALUE!
17KWRHTBBG_KWRHTBBGOL0-XGS-PON-1/1/16/10KWRHTBBG_KWRHTBBGOL0#VALUE!#VALUE!#VALUE!#VALUE!
18KWRHTBBG_KWRHTBBGOL0-XGS-PON-1/1/16/11KWRHTBBG_KWRHTBBGOL0#VALUE!#VALUE!#VALUE!#VALUE!
19KWRHTBBG_KWRHTBBGOL0-XGS-PON-1/1/16/12KWRHTBBG_KWRHTBBGOL0#VALUE!#VALUE!#VALUE!#VALUE!
20KWRHTBBG_KWRHTBBGOL0-XGS-PON-1/1/16/4KWRHTBBG_KWRHTBBGOL0#VALUE!#VALUE!#VALUE!#VALUE!
21KWRHTBBG_KWRHTBBGOL0-XGS-PON-1/1/16/5KWRHTBBG_KWRHTBBGOL0#VALUE!#VALUE!#VALUE!#VALUE!
22KWRHTBBG_KWRHTBBGOL0-XGS-PON-1/1/16/6KWRHTBBG_KWRHTBBGOL0#VALUE!#VALUE!#VALUE!#VALUE!
Sheet1
Cell Formulas
RangeFormula
B2:B22B2=IF(ISNUMBER(FIND("-GPON",A2)),LEFT(A2,FIND("-GPON",A2)-1),LEFT(A2,FIND("-XGS",A2)-1))
C2:C22C2=MID(A2,FIND("_",A2)+1,FIND("_",A2,FIND("_",A2)+1)-FIND("_",A2)-1)
D2:D22D2=MID(A2,FIND("_",A2)+1,FIND("_",A2,FIND("_",A2)+1)-FIND("_",A2)-1)
E2:E22E2=MID(A2,FIND("_",A2,FIND("_",A2)+1)+1,FIND("_",A2,FIND("_",A2,FIND("_",A2)+1)+1)-FIND("_",A2,FIND("_",A2)+1)-1)
F2:F22F2=MID(A2,FIND("/",A2,FIND("/",A2,FIND("/",A2,FIND("/",A2)+1)+1)+1)+1,LEN(A2))
 
Upvote 0
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.
 
Upvote 0
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.
trumpexcel.com

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 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.
 
Upvote 0
How about
Fluff.xlsm
ABCDEF
1InputOutput 1Output 2Output 3Output 4Output 5
2707-00_BYSHJZ00OL0-GPON-1/1/1/6707-00_BYSHJZ00OL01116
3117-00_ESKNRD00OL3-GPON-1/1/13/1117-00_ESKNRD00OL311131
4707-00_BYSHJZ00OL0-GPON-1/1/1/7707-00_BYSHJZ00OL01117
5414-00_KYBRDN00OL1-GPON-1/1/16/1414-00_KYBRDN00OL111161
6241-00_RABGKH00OL1-GPON-1/1/16/2241-00_RABGKH00OL111162
7704-00_KARTJZAAOL0-GPON-1/1/3/4704-00_KARTJZAAOL01134
8704-00_KARTJZAAOL0-GPON-1/1/4/1704-00_KARTJZAAOL01141
9231-00_SHBRKH00OL1-GPON-1/1/1/7231-00_SHBRKH00OL11117
10701-00_KSHARR00OL7-GPON-1/1/8/8701-00_KSHARR00OL71188
11418-00_ESKNDN00OL5-GPON-1/1/11/10418-00_ESKNDN00OL5111110
12405-41_QDSYTBAAOL4-GPON-1/1/1/8405-41_QDSYTBAAOL41118
13213-00_MSLMKH00OL2-GPON-1/1/5/16213-00_MSLMKH00OL211516
14212-00_SHRFKH00OL8-GPON-1/1/6/5212-00_SHRFKH00OL81165
15428-00_DEFADN00OL2-GPON-1/1/3/3428-00_DEFADN00OL21133
16232-00_HWAYKH00OL2-GPON-1/1/7/1232-00_HWAYKH00OL21171
17KWRHTBBG_KWRHTBBGOL0-XGS-PON-1/1/16/10KWRHTBBG_KWRHTBBGOL0111610
18KWRHTBBG_KWRHTBBGOL0-XGS-PON-1/1/16/11KWRHTBBG_KWRHTBBGOL0111611
19KWRHTBBG_KWRHTBBGOL0-XGS-PON-1/1/16/12KWRHTBBG_KWRHTBBGOL0111612
20KWRHTBBG_KWRHTBBGOL0-XGS-PON-1/1/16/4KWRHTBBG_KWRHTBBGOL011164
21KWRHTBBG_KWRHTBBGOL0-XGS-PON-1/1/16/5KWRHTBBG_KWRHTBBGOL011165
22KWRHTBBG_KWRHTBBGOL0-XGS-PON-1/1/16/6KWRHTBBG_KWRHTBBGOL011166
Sheet5
Cell Formulas
RangeFormula
B2:B22B2=LEFT(A2,FIND("-",A2,FIND("_",A2))-1)
C2:C22C2=FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE($A2,"-","/"),"/","</m><m>")&"</m></k>","//m[.=number()][position()=last()-3]")
D2:D22D2=FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE($A2,"-","/"),"/","</m><m>")&"</m></k>","//m[.=number()][position()=last()-2]")
E2:E22E2=FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE($A2,"-","/"),"/","</m><m>")&"</m></k>","//m[.=number()][position()=last()-1]")
F2:F22F2=FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE($A2,"-","/"),"/","</m><m>")&"</m></k>","//m[.=number()][position()=last()]")
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

V
  • Solved
SUBSTITUTE function
Replies
3
Views
298
Fluff
Fluff

Forum statistics

Threads
1,224,813
Messages
6,181,115
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top