CODEWITHSUNDEEP
excelreplaceexcel-formulamicrosoft365
Wheeeeeecode
Wheeeeeecode

Reputation: 57

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

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)?

Upvotes: 0

Views: 428

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60199

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:

enter image description here

Explanation of the regex:

remAbbrevs

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

Options: Case sensitive

Created with RegexBuddy

Upvotes: 2

Related Questions