Ever wanted to extract a sub string from a cell in Microsoft Excel using a Regular expression? Here’s a Visual Basic snippet that will do just that.
Function getRegexGroup(inRegex As String, inValue As String, inGroup As Integer) Set myRegExp = CreateObject("vbscript.regexp") myRegExp.IgnoreCase = True myRegExp.Global = True myRegExp.Pattern = inRegex Set myMatches = myRegExp.Execute(inValue) Set SubMatches = myMatches.Item(0).SubMatches getRegexGroup = SubMatches.Item(inGroup) End Function |
You can use it like so:
=getRegexGroup([REGULAR EXPRESSION], [INPUT STRING], [GROUP NUMBER TO EXTRACT])
Hi Rob. Love the snippet. If you could please help with the following, it will truly end my search, my reading, my trials and errors.
If we had “Hello Bob it’s Tim. Ah yes, Hello Tim it’s Bob” in one cell.
Is there a way to get Bob, after the word Hello, and Tim after the second word Hello.
Can we modify your code to get all iterations of our sub string.
Thanks in advance.
Rex
Hi Rex,
You could use a regex like this
(?:Hello (.+?)\b)*and iterate the groups. Here’s a nice website to try it out in http://www.gethifi.com/tools/regexSorry it took me a while to reply, I’ve been busy 🙂