remove after last "-" not working

[dmj120]

I'm trying to remove text after the second hyphen. Some cells have only one hyphen, while others have two. The below formula works with one hyphen

in C2 I have =LEFT(B2,FIND("@",SUBSTITUTE(B2,"-","@",LEN(B2)-LEN(SUBSTITUTE(B2,"character",""))))-1)

Example:

Col B	Col C - desired result
sicu - r2	sicu
burnt - d9	burnt
A3 - OPP - R7	A3 - OPP
QPP - Ops - dev9	OPP - Ops
QPP - Ops - dev9	OPP ----- what my formula outputs

Where am I making a mistake?

Thanks
Josh

 

[jtakw]

Hi,

If I understand correctly:

Book3.xlsx
	B	C
1
2	sicu - r2	sicu - r2
3	burnt - d9	burnt - d9
4	A3 - OPP - R7	A3 - OPP
5	QPP - Ops - dev9	QPP - Ops
6	QPP - Ops - dev9	QPP - Ops
Sheet937

Cell Formulas
Range	Formula
C2:C6	C2	=TRIM(LEFT(SUBSTITUTE(B2,"-",REPT(" ",99),2),99))

 

[dmj120]

Hi,

If I understand correctly:

Book3.xlsx
	B	C
1
2	sicu - r2	sicu - r2
3	burnt - d9	burnt - d9
4	A3 - OPP - R7	A3 - OPP
5	QPP - Ops - dev9	QPP - Ops
6	QPP - Ops - dev9	QPP - Ops
Sheet937

Cell Formulas
Range	Formula
C2:C6	C2	=TRIM(LEFT(SUBSTITUTE(B2,"-",REPT(" ",99),2),99))

Not quite. Most of the cells in col.C only have have one hyphen, which works correctly.

In your example C4-C6 are correct, but C2 and C3 needs to be just sicu and burnt, respectively. This is the challenge I've been having.

 

[Peter_SSs]

Like this?

22 01 26.xlsm
	B	C
1
2	sicu - r2	sicu
3	burnt - d9	burnt
4	A3 - OPP - R7	A3 - OPP
5	QPP - Ops - dev9	QPP - Ops
6	QPP - Ops - dev9	QPP - Ops
dmj120

Cell Formulas
Range	Formula
C2:C6	C2	=TRIM(LEFT(B2,FIND("@",SUBSTITUTE(B2,"-","@",LEN(B2)-LEN(SUBSTITUTE(B2,"-",""))))-1))

 

[jtakw]

Not quite. Most of the cells in col.C only have have one hyphen, which works correctly.

In your example C4-C6 are correct, but C2 and C3 needs to be just sicu and burnt, respectively. This is the challenge I've been having.

Well, the confusion is, your original formula, aside from the typo "character", should be "-" works:

Book3.xlsx
	B	C
1
2	sicu - r2	sicu
3	burnt - d9	burnt
4	A3 - OPP - R7	A3 - OPP
5	QPP - Ops - dev9	QPP - Ops
6	QPP - Ops - dev9	QPP - Ops
Sheet937

Cell Formulas
Range	Formula
C2:C6	C2	=LEFT(B2,FIND("@",SUBSTITUTE(B2,"-","@",LEN(B2)-LEN(SUBSTITUTE(B2,"-",""))))-1)

 

[dmj120]

Well, the confusion is, your original formula, aside from the typo "character", should be "-" works:

holly cannoli ..... many thanks!!!

 

[Peter_SSs]

your original formula, aside from the typo "character", should be "-" works:

.. apart from it leaves (or may leave) a space character on the end which I assumed would not be wanted - hence added the TRIM function.

 

[dmj120]

.. apart from it leaves (or may leave) a space character on the end which I assumed would not be wanted - hence added the TRIM function.

Thanks Peter!!! I wasn't thinking about an ending space. ??

 

[jtakw]

Well, the confusion is, your original formula, aside from the typo "character", should be "-" works:

.. apart from it leaves (or may leave) a space character on the end which I assumed would not be wanted - hence added the TRIM function.

I was just addressing why I was not sure, or confused, since OP formula works, therefore I thought OP wanted Only to remove after 2nd "-"

Regarding the extra space OP can change "-1" to "-2" in formula, or use TRIM as you suggested.

 

[Rick Rothstein]

Here is another formula that you can try...

=TRIM(LEFT(B2,IFERROR(FIND("@",SUBSTITUTE(B2,"-","@",2)),FIND("-",B2))-1))