Excel: Find and Replace without also grabbing the beginning of another word

Question

I'm currently working on shortening a large excel sheet using Find/Replace. I'm finding all instances of words like ", Inc.", ", Co." " LLC", etc. and replacing them with nothing (aka removing them). The problem I am having is that I'm unable to do similar searches for " Inc", ", Inc", ", Co", etc. and remove them because it will also remove them the beginnings of words like ", Inc"orporated, and ", Co"mpany.

Is there a blank character or something I can do in VBA that would allow me to just find/replace items with nothing after what I'm finding (I.e. finding ", Co" without also catching ", Co"rporated)?

Including the preceding and following characters in your search criteria would solve that problem. You could temporarily add a character at the end of all company names to included in the search to find matches at the end of names. Your search items aren't likely to occur at the beginning of a name but if they do you could also add a special character at the beginning which you remove if the replacement didn't do it. Choose a character that doesn't occur in the names. — Variatus, Jul 20 '20 at 01:08

Ron Rosenfeld · Answer 1 · 2020-07-20T02:21:36.900

In VBA you can use Regular Expressions to ensure that there are "word boundaries" before and after the abbreviation you are trying to remove. You can also remove extraneous spaces that might appear, depending on the original string.

Function remAbbrevs(S As String, ParamArray abbrevs()) As String
    Dim RE As Object
    Dim sPat As String

sPat = "\s*\b(?:" & Join(abbrevs, "|") & ")\b\.?"
Set RE = CreateObject("vbscript.regexp")
With RE
    .Global = True
    .ignorecase = False
    .Pattern = sPat
    remAbbrevs = .Replace(S, "")
End With

End Function

For arguments to this function you can enter a series of abbreviations. The function creates an appropriate regex to use.

For example in the below, I entered:

=remAbbrevs(A1,"Inc","Corp")

and filled down:

Explanation of the regex:

remAbbrevs

\s*\b(?:Inc|Corp)\b\.?

Options: Case sensitive

Created with RegexBuddy

Excel: Find and Replace without also grabbing the beginning of another word

1 Answers1

remAbbrevs