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
[^(Instructor)]
might work