Returning a regex match in VBA (excel)

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 StringDim result As StringDim allMatches As ObjectDim RE As ObjectSet RE = CreateObject("vbscript.regexp")RE.pattern = "(sdi \d+)"RE.Global = TrueRE.IgnoreCase = TrueSet allMatches = RE.Execute(text)If allMatches.count <> 0 Then    result = allMatches.Item(0).submatches.Item(0)End IfExtractSDI = resultEnd 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 StringDim i As Long, j As LongDim result As StringDim allMatches As ObjectDim RE As ObjectSet RE = CreateObject("vbscript.regexp")RE.pattern = extract_whatRE.Global = TrueSet 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)    NextNextIf Len(result) <> 0 Then    result = Right(result, Len(result) - Len(seperator))End IfRegexExtract = resultEnd 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.