Excluding text during VBA (Excel) RegEx search

I have a function inside a macro using regular expression in Excel 2016 (VBA) that should remove all numbers from the text so that I basically end up with only alphabetic characters. The catch is that these numbers are not just digits, but they can also be Roman numerals (only including Roman numerals one through four, which is I, II, III and IV). As an example, take the following list of possible items:

Program Manager 3
Systems Engineer 3
Secretary III 12345
Consultant
IT Instructor 3
Computer Operations Manager 1
User Support Specialist 2
Engineering Tech II 12345
IT Instructor 2
Network Tech 3

My function uses the following VBA regular expression code to replace the digits and Roman numerals (I’m not worried about trimming or anything at this point):

Public Function RemoveNumbers(Txt As String) As String With CreateObject("VBScript.RegExp") .Global = True .IgnoreCase = True .Pattern = "[0-9]|\s[i]+|\s[iv]$" RemoveNumbers = .Replace(Txt, "") End With
End Function

Generally, that works OK except I’ve run into one problem. My RegEx incorrectly alters the phrase IT Instructor 2 and turns it into ITnstructor (because of the space and then the word Instructor, which starts with an I which is the same as Roman numeral one). I’ve tried finding the answer online and have tested many variations to get RegEx to exclude the phrase Instructor in the search but I can’t get it to work. Some of the patterns that I’ve tried to use include:

 .Pattern = "\b(!Instructor)\b|[0-9]|\s[i]+|\s[iv]$" .Pattern = "\b(!Instructor)\b\w+|[0-9]|\s[i]+|\s[iv]$" .Pattern = "(!Instructor\b)|[0-9]|\s[i]+|\s[iv]$"
...etc

And since I have to remove the Roman numeral one (I), I can’t use the following as a workaround:

 .Pattern = "[0-9]|\s[i]{2,}|\s[iv]$"

Is it possible to exclude a string (such as Instructor) from being part of the search using Excel 2016 VBA regular expressions? If so, can someone point me in the correct direction on how to exclude items during a VBA RegEx?

Thanks

1 Answer

I figured it out. The following syntax works for me (lots of trial and error):

 .Pattern = "\b(?!(?:Instructor)\b)(?:[0-9]+|\s[i]+|\s[iv]$)\b"

-- EDITED to add the details below --

I added an additional word (i.e., Info) to the RegEx exclusion:

"\b(?!(?:Info|Instructor)\b)(?:[0-9]+|\s[i]+|\s[iv]$)\b"

Details of the pattern:

• \b - Set word boundary
• (?!(?:Info|Instructor)\b) - Zero or one (?) exclusions (!) for the words that follow. Non-capturing group because we don't want them. The \b at the end is a word boundry
• (?:[0-9]+|\s[i]+|\s[iv]$) - Match one or more 0 - 9 digits. Match a space (\s) followed by one or more i characters. Match a space followed by iv ($ says search at the end)
• | = OR (used throughout)
• \b - Trailing word boundary

-- EDITED because ultimately, this worked best for me --

 .Pattern = "\b(?:[0-9]+|\s[i]+|\s[iv]+$)\b"