Help with FIND

[djl0525]

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

SAMPLE DATA	RESULT
1-2-3-4	3
11-2-333-4	333
1-2222-3-4444	3
11111111-22222222-33333333-44444444	33333333

<tbody>
</tbody>

Any assistance will be greatly appreciated!

DJ

 

[sandy666]

you can try Power Query

SAMPLE DATA		Text Between Delimiters
1-2-3-4		3
11-2-333-4		333
1-2222-3-4444		3
11111111-22222222-33333333-44444444		33333333

[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