Silly Excel Formula Question (having a brain freeze moment)

**rjw524** · 03-05-2015, 02:20 PM

Hi,

For some reason, I can't recall how to do something that I think is pretty simple in Excel.

I'm cleaning data (company names in particular) and I'm trying to remove the generic "Co" from the end of a company's name. So I did a simple substitute formula and put a space before "Co" to avoid removing it wholesale from the list. (i.e. "Costco" becoming "st")

=SUBSTITUTE(A1," Co","")

Problem is it's also removing "Co" from Company, Corp, Corporation, Cos., Cos, etc.

So I'm getting "General Motors rporation" or "ABC mpanies"

I really don't wanna have to write some long string handling formula. (I'm just...not in the mood, lol) but I'm thinking the answer is much simpler, right? Like a wildcard I'm forgetting or something.

I know someone here knows the answer, lol.

Thanks!

R.J.

**rjw524** · 03-05-2015, 02:34 PM

I decided to bite the bullet and just do the long formula thingy da**it...

=TRIM(IF((RIGHT(A2,3)=" Co"),LEFT(TRIM(A2),FIND("~",SUBSTITUTE(A2," ","~",LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))))-1),A2))

Isn't there a wildcard that means "this text by itself only and not as part of another word"?

**Pete_UK** · 03-05-2015, 02:36 PM

If it is always at the end of the name, perhaps you can use LEFT up to the last space.

Hope this helps.

Pete

**ChemistB** · 03-05-2015, 02:46 PM

There is no wildcard for not part of another word. Maybe " co " with spaces at both ends is what you are looking for.

Or
IF(RIGHT(A1,3)= " co"), LEFT(A1, LEN(A1)-3, IF(ISNUMBER(SEARCH(A1, " co ")), SUBSTITUTE(A1, " co ", ""), A1))