Extract Data before the 2nd to last dash

[SuzanneHA]

Hello,

I have been really struggling with this, I need a formula that can extract the data before the 2nd to last dash.

I have thousands of lines of data, and the data are all different lengths, with different dashes.

Here is an example of data and what I need to extract


[TABLE="width: 500"]
<tbody>[TR]
[TD]Data Now[/TD]
[TD]Extract[/TD]
[/TR]
[TR]
[TD]SH-RED-54541-2-LONG[/TD]
[TD]SH-RED-54541[/TD]
[/TR]
[TR]
[TD]JH-BLUE-12112-FG-4-LONG[/TD]
[TD]JH-BLUE-12112-FG[/TD]
[/TR]
[TR]
[TD]PO-DFG-3643-8-SHORT[/TD]
[TD]PO-DFG-3643[/TD]
[/TR]
</tbody>[/TABLE]



Thanks
Suzanne

 

[Scott Huish]

=trim(left(substitute(a1,"-",rept(" ",255),len(a1)-len(substitute(a1,"-",""))-1),255))

 

[SuzanneHA]

Thank you!