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 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.
Admin
Updated on July 09, 2022Comments
-
Admin almost 2 years
I am trying to write a function for excel 2010 that will take a cell of unstructured text, look for something called an sdi value and, if found, return that number. The sdi value will appear as sdi ####. What I want is to return sdi and the sepecific numbers that follow it, so if the cell contains "some text sdi 1234 some more text" the function will return sdi 1234.
This is my function:
Function SdiTest(LookIn As String) As String Dim temp As String Dim STA As Object temp = "" Set SDI = CreateObject("VBScript.RegExp") SDI.IgnoreCase = True SDI.Pattern = "sdi [1-9]*" SDI.Global = True If SDI.Test(LookIn) Then temp = SDI.Execute(LookIn) End If SdiTest = temp End Function
If there is no sdi number it never enters the if statement and dutifully returns the empty string. If there is an sdi number I get #VALUE!
What am I missing?
Yes, VBScript is enabled. Additionally, I am finding it frustrating to use regex in VBA, and hard to find useful info online. Links to good online resources would be appreciated.
Thank you
-
aevanko over 12 yearsTheoRose, you're welcome. If the answer works, you can click the arrow on the upper left of it to 'accept it'. :)
-
Reafidy over 12 years+1, Good stuff. But why are you disabling screenupdating within the function?
-
aevanko over 12 yearsYou're right, Reafidy. I took it out. I think it's just out of habbit that I included it :) (a good habit to have, though)
-
DataWriter over 5 yearsAnswers like this are why StackOverflow exists. Thank you so much for saving me the time of trying to roll my own.