Returning a regex match in VBA (excel)

by

You need to access the matches in order to get at the SDI number. Here is a function that will do it (assuming there is only 1 SDI number per cell).

For the regex, I used “sdi followed by a space and one or more numbers”. You had “sdi followed by a space and zero or more numbers”. You can simply change the + to * in my pattern to go back to what you had.

Function ExtractSDI(ByVal text As String) As String

Dim result As String
Dim allMatches As Object
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")

RE.pattern = "(sdi \d+)"
RE.Global = True
RE.IgnoreCase = True
Set allMatches = RE.Execute(text)

If allMatches.count <> 0 Then
    result = allMatches.Item(0).submatches.Item(0)
End If

ExtractSDI = result

End Function

If a cell may have more than one SDI number you want to extract, here is my RegexExtract function. You can pass in a third paramter to seperate each match (like comma-seperate them), and you manually enter the pattern in the actual function call:

Ex) =RegexExtract(A1, "(sdi \d+)", ", ")

Here is:

Function RegexExtract(ByVal text As String, _
                      ByVal extract_what As String, _
                      Optional seperator As String = "") As String

Dim i As Long, j As Long
Dim result As String
Dim allMatches As Object
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")

RE.pattern = extract_what
RE.Global = True
Set allMatches = RE.Execute(text)

For i = 0 To allMatches.count - 1
    For j = 0 To allMatches.Item(i).submatches.count - 1
        result = result & seperator & allMatches.Item(i).submatches.Item(j)
    Next
Next

If Len(result) <> 0 Then
    result = Right(result, Len(result) - Len(seperator))
End If

RegexExtract = result

End Function

*Please note that I have taken “RE.IgnoreCase = True” out of my RegexExtract, but you could add it back in, or even add it as an optional 4th parameter if you like.

More Related Contents:

  1. How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops
  2. Splitting String in VBA using RegEx
  3. How do I get regex support in Excel via a function, or custom function?
  4. RegEx with Excel VBA on Mac
  5. html parsing of cricinfo scorecards
  6. How to Use/Enable (RegExp object) Regular Expression using VBA (MACRO) in word
  7. Lookbehind on regex for VBA?
  8. Format Cell Contents
  9. Excel formula checklist in employees
  10. Phone number regular expressions exact format
  11. How do I put double quotes in a string in vba?
  12. How can I URL encode a string in Excel VBA?
  13. Does a dot have to be escaped in a character class (square brackets) of a regular expression?
  14. Embed picture in outlook mail body excel vba
  15. Paste Excel range in Outlook
  16. Searching an excel with two search words (multiple lookup) using vba macro [closed]
  17. Is it possible to fill an array with row numbers which match a certain criteria without looping?
  18. regex to match word boundary beginning with special characters
  19. Does the ‘o’ modifier for Perl regular expressions still provide any benefit?
  20. Excel VBA: Range to String Array in 1 step
  21. Run-time error ‘1004’ : Method ‘Range’ of object’_Global’ failed
  22. Export chart as image – with click of a button
  23. Error13 in Excel VBA in data file
  24. Excel cell from which a Function is called [duplicate]
  25. How can I parse quoted CSV in Perl with a regex?
  26. How to extract text within a string of text
  27. Checking for diacritics with a regular expression
  28. Excel VBA like operator [closed]
  29. Regex Letters, Numbers, Dashes, and Underscores
  30. How to replace only part of found text?

Leave a Comment