Formula to result text between last two 'dash' ticks

[Dojorgen]

Hey guys,

always get me great feedback I have:

Cell C2:
/123K/XXX0-A/XXXX0-135-A0.job

I was looking for a formula that would return the value 135 in this example.

It will always be between the last two dashes in the cell.

Any suggestions would be much appreciated

 

[Michael M]

Try

=TRIM(MID(SUBSTITUTE(C5,"-",REPT(" ",100)),200,100))

 

[BobUmlas]

You can always try Flash Fill... Enter manually the 135 in a cell in the same row, like D2, then press Ctrl/E to have Flash Fill use the patterning to do the rest in column C.

I did find a formula solution but it was 50 characters!

 

[BobUmlas]

I meant 520 characters!

 

[Dojorgen]

Sorry should have given you all examples:
/XXXX0/XXXX0-330-B0

Is there something i can use for both?

 

[jtakw]

Hi,

Another way, in case you have varying length or text containing varying numbers of "hyphens".

D2 formula converts results to Real Numbers.
E2 formula results in Text.

Book1
	C	D	E
1		Numeric	Text
2	/123K/XXX0-A/XXXX0-135-A0.job	135	135
3	123K/XXX0-A/XXXX0-B/XXXXX0-C/X0-136-XX.job	136	136
4	/XXXX0/XXXX0-330-B0	330	330
Sheet466

Cell Formulas
Range	Formula
D2	=MID(SUBSTITUTE(C2,"-",REPT(" ",99)),(LEN(C2)-LEN(SUBSTITUTE(C2,"-",""))-1)*99,99)+0
E2	=TRIM(MID(SUBSTITUTE(C2,"-",REPT(" ",99)),(LEN(C2)-LEN(SUBSTITUTE(C2,"-",""))-1)*99,99))

EDIT: included your latest sample above, these formulas will work for ANY length text string.

 

[Dojorgen]

thanks for the feedback but the three digits varies cell to cell

 

[Dojorgen]

Ahh cool thanks for all the help guys works perfect as always!!!!

 

[Michael M]

Try

=TRIM(LEFT(RIGHT(SUBSTITUTE(C5,"-",REPT(" ",100)),200),100))

 

[jtakw]

Hey Michael, just realized I made it more complicated than it needed to be...

Just in case OP wants results numeric, using your formula above:

=LEFT(RIGHT(SUBSTITUTE(C2,"-",REPT(" ",100)),200),100)+0