How do I remove everything after the hyphen when the hyphen is sometimes the second hyphen?

[kesposito]

We use a third party vendor to store and access our data. When running one key report it used to turn out a field of company names. We have a excel report that looks for their names in reports and counts them.

Some of the names are BRCM, Community Resource Center, or Hemingwey's Place. But some have hyphens in the name like BR-Corp or NJ-State, which is alright because we set up the report to count them.

Now the vendor has revamped their site and now that field produces both the company name and the contact name.
So now the fields comes out as BCRM - Margaret Jones or BR-CORP - Sam Johnson.

I can run a formula to get rid of the hyphen and everything after the hyphen but that would eliminate everything after the hyphen in name that naturally have the hyphen.

So I would get BCRM AND BC, when I need BCRM and BR-CORP

Other than yelling at my vendor, is there a formula to remove the hyphen and everything after only after a certain point and preserve entries where the hyphen should be there?

Thanks

 

[boblarson]

Try this formula

=LEFT(A1,LOOKUP(2,1/(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="-"),
ROW(INDIRECT("1:"&LEN(A1))))-1)

 

[dms37]

Is the format always a space, hyphen then space before the contact name?

If it is maybe try something like:

=LEFT(A2,SEARCH(" - ",A2)-1)

I can still see you may get the odd error with company names having spaces and hyphens - guess it depends how large of a report it is that will decide if you need it to catch all the scenarios or if the odd mismatch can be picked out manually.

 

[kesposito]

Is the format always a space, hyphen then space before the contact name?

If it is maybe try something like:

=LEFT(A2,SEARCH(" - ",A2)-1)

I can still see you may get the odd error with company names having spaces and hyphens - guess it depends how large of a report it is that will decide if you need it to catch all the scenarios or if the odd mismatch can be picked out manually.

That worked. Thanks!