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

 

[dmj120]

Here is another formula that you can try...

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

Thanks Rick. I ended up with
=IFERROR(TRIM(LEFT(B2,FIND("@",SUBSTITUTE(B2,"-","@",LEN(B2)-LEN(SUBSTITUTE(B2,"-",""))))-1)),B2)

Why did you put iferror inside the 'left trim' ? I've never used iferror inside, only as the catch-all in front of whatever formula i'm creating.

 

[Rick Rothstein]

Why did you put iferror inside the 'left trim' ? I've never used iferror inside, only as the catch-all in front of whatever formula i'm creating.

IFERROR is a function that returns a value (either text, number, date, whatever) and, as such, can be used anywhere within a formula provided the rest of the formula is designed to make use of what it returns. In my formula, the first part of the IFERROR finds the location of the second dash... if that errors out, then it means there is only one dash and the second part of the IFERROR calculates where that dash is. So, a number is returned either way and the LEFT function uses it.

 

[dmj120]

IFERROR is a function that returns a value (either text, number, date, whatever) and, as such, can be used anywhere within a formula provided the rest of the formula is designed to make use of what it returns.

In my formula, the first part of the IFERROR finds the location of the second dash... if that errors out, then it means there is only one dash and the second part of the IFERROR calculates where that dash is. So, a number is returned either way and the LEFT function uses it.

Thanks for the explanation!
I finally feel like I'm crossing into an intermediate level..... I have an idea of how much I don't know. ?

.........getting a semi-decent 17K row file to standardize, which is then followed by a 29.5K row file (in a different format, with new and duplicate items), not to mention after file #1 was 72% complete......well, it's gotta be scotch-frickin-thirty! ? ??

 

[Peter_SSs]

You could also do it without the introduction of a new character

=TRIM(LEFT(B2,IFERROR(FIND("-",B2,FIND("-",B2)+1),FIND("-",B2))-1))

 

[jtakw]

Also without the TRIM, and threw in the IFERROR for no hyphen:

Book3.xlsx
	B	C
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
7	abc	abc
8	abc - def - gh - ij -klm	abc - def
Sheet937

Cell Formulas
Range	Formula
C2:C8	C2	=IFERROR(LEFT(B2,IFERROR(FIND("-",B2,FIND("-",B2)+1)-1,FIND("-",B2)-1)-1),B2)

 

[Rick Rothstein]

You could also do it without the introduction of a new character

Good point !!!

 

[Rick Rothstein]

Also without the TRIM, and threw in the IFERROR for no hyphen:
]

I would leave the TRIM from Peter's reworking of my formula in just in case the space before the dash was not always there. As for handling the "no dash" error, I would just give that last FIND function call a dash to find for those cases. So, modifying what Peter posted, this will return what the OP wanted and return the same values your formula returned for those that do not have a dash...

=TRIM(LEFT(B2,IFERROR(FIND("-",B2,FIND("-",B2)+1),FIND("-",B2&"-"))-1))

I think given the combined contributions from the three of us, this formula should be the final form for the OP to use.

 

[dmj120]

Also without the TRIM, and threw in the IFERROR for no hyphen:

=IFERROR(LEFT(B2,IFERROR(FIND("-",B2,FIND("-",B2)+1)-1,FIND("-",B2)-1)-1),B2)

That is awesome! I'm going to play with this so I can "read" it and absorb it. eg. I "read" the if() as "if this, than that, otherwise......etc.

 

[Rick Rothstein]

That is awesome! I'm going to play with this so I can "read" it and absorb it. eg. I "read" the if() as "if this, than that, otherwise......etc.

Just noting I posted something regarding Josh's formula in Message #17 that you may want to read.

 

[jtakw]

Just noting I posted something regarding Josh's formula in Message #17 that you may want to read.

Hey Rick, how'd you come up with Josh ?, unless you're referring to someone else, but you're close, it's Joseph.

And Yes, your latest formula post would be the shortest so far