Macro to Parse Company Names

I'm building a Macro to parse company names in an excel database. The purpose is to remove common company extensions such as Inc, Ltd, Co, Corp, etc. As an example, ABC Inception Ltd. should becomes ABC Inception. The problem I'm having is that I can't seem to eliminate spaces at the end of the fields and I can't get the macro to ignore "Inc" when its part of a larger word so that my actual output for the preceding example would be "ABC eption ".

The code fragment I'm using looks like this:

Range("C:C").Replace What:="inc", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:= _
False

Thoughts?

Read the company name from the right for
Inc
Inc.
Ltd
Ltd.
etc...
Depending if it's 4 or 3 characters, use len and mid to replace the cel value.

Something like this...

Please Login or Register  to view this content.