Using LEFT function

[antruse]

Hi,

I would like to extract data using left function, but i have to get all the text before the last "-" symbol. Please help me. (note my text has multiple "-" symbol in between..
see below example:

CAB0-PA-01137-010-01b
CAB0-PA-01137-010-01ff
CAAA-LSC-09109-011-0gt1
CAAA-LSC-09109-011-0155
CAAA-LSC-09109-011-01hh
CAAA-PA-01120-010-01jd
CAAA-PA-01160-010-01
CAAA-RV-01114-010-012
CAAA-RV-01130-010-1.


My answer should be like this:

CAB0-PA-01137-010
CAB0-PA-01137-010
CAAA-LSC-09109-011
CAAA-LSC-09109-011
CAAA-LSC-09109-011
CAAA-PA-01120-010
CAAA-PA-01160-010
CAAA-RV-01114-010
CAAA-RV-01130-010


Thanks for your replies.

 

[Somendra Misra]

Hi,

Considering your data in A1, try below formula:

=LEFT(A1,FIND("|",SUBSTITUTE(A1,"-","|",LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))))-1)

Regards,

 

[Deepak]

This one too..

=LEFT(A1,LEN(A1)-1-LEN(TRIM(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",99)),99))))

 

[John Jairo V]

Hi to all!

Another one:
=REPLACE(A1,FIND("|",SUBSTITUTE(A1,"-","|",LEN(A1)-LEN(SUBSTITUTE(A1,"-","")))),99,"")

Blessings!

 

[shrivallabha]

Text functions always prove to be an exercise. One more:
=SUBSTITUTE(A1,"-"&TRIM(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",99)),99)),"")
Oops: Fails on 7th entry due to identical numbers.

 

[antruse]

Dear All,
Thanks to all your contributions...