Help with FIND

[djl0525]

I need a formula to extract the characters between the second and third hyphen in the strings below.

[TABLE="width: 500"]
<tbody>[TR]
[TD]SAMPLE DATA[/TD]
[TD]RESULT[/TD]
[/TR]
[TR]
[TD]1-2-3-4[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]11-2-333-4[/TD]
[TD]333[/TD]
[/TR]
[TR]
[TD]1-2222-3-4444[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]11111111-22222222-33333333-44444444[/TD]
[TD]33333333[/TD]
[/TR]
</tbody>[/TABLE]

Any assistance will be greatly appreciated!

DJ

 

[sandy666]

you can try Power Query

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]SAMPLE DATA[/td][td][/td][td=bgcolor:#70AD47]Text Between Delimiters[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]1-2-3-4[/td][td][/td][td=bgcolor:#E2EFDA]3[/td][/tr]

[tr=bgcolor:#FFFFFF][td]11-2-333-4[/td][td][/td][td]333[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]1-2222-3-4444[/td][td][/td][td=bgcolor:#E2EFDA]3[/td][/tr]

[tr=bgcolor:#FFFFFF][td]11111111-22222222-33333333-44444444[/td][td][/td][td]33333333[/td][/tr]
[/table]

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Between = Table.AddColumn(Source, "Text Between Delimiters", each Text.BetweenDelimiters([SAMPLE DATA], "-", "-", 1, 0), type text),
    ROC = Table.SelectColumns(Between,{"Text Between Delimiters"})
in
    ROC[/SIZE]

 

[AlphaFrog]

Try this...

=TRIM(MID(SUBSTITUTE(A2,"-",REPT(" ",100)),200,100))*1

The *1 is to convert the result to a numeric value.

 

[djl0525]

Thank you!

 

[djl0525]

Worked like a charm! Thank you!!

 

[djl0525]

AlphaFrog, can you translate your formula to English for me, please? I'd like to understand it.

Try this...

=TRIM(MID(SUBSTITUTE(A2,"-",REPT(" ",100)),200,100))*1

The *1 is to convert the result to a numeric value.

DeLisa

 

[AlphaFrog]

AlphaFrog, can you translate your formula to English for me, please? I'd like to understand it.

The gist of the formula is to replace a given delimiter with a large number of spaces using SUBSTITUTE and REPT, then use the MID function to extract text related to the 2nd occurrence and then the TRIM function to get rid of the extra spaces.

This site explains it well...
https://exceljet.net/formula/split-text-with-delimiter