Call regex in an Excel cell
I want to extract a string from a cell using a regex match. I can't find the excel function that does it and I'm no VBA expert开发者_运维知识库 either. I use Excel 2007.
In the Excel VBA code editor select Tools/References. In the picker check the latest version of Microsoft VBScript Regular Expresions.
Here is a simple user defined function to get you started. This will return "foo" if you point it at a cell with "foobar" or "fooooobar" in it.
Function RegExtract(contents As String)
Dim regx As RegExp
Set regx = New RegExp
With regx
.Pattern = "f[o]+"
With .Execute(contents)
If .Count Then RegExtract = .Item(0).Value
End With
End With
End Function
Here's a function that will let you pass in a cell then any regex pattern. Use () around the part of the pattern you want the function to return.
Function RegexExtract(ByVal text As String, _
ByVal extract_what As String) 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)
RegexExtract = allMatches.Item(0).submatches.Item(0)
End Function
There's a library available in Excel's VBA system known as Microsoft VBScript Regular Expressions. Once you include the library from the internal VBA 'IDE', you can create a simple macro that accepts whatever value you'd like to parse and return the result back to the caller (which can be a function call from a cell).
@AndrewCowenhoven Has a good answer. It's simple to add the Regex Library. Just to expand this answer a little, I will add what I learned to get items within matches.
For example, I have a string like this in one cell.
doubleclick.net/activityi;u=5f2256de37ab4992927b3a0a0a34f983;u13=;u14=27.34;u16=AUD;u9=;u10=;u11=;u12=;u5=;u6=;u7=;u8=;u1=Car;u2=SEA;u3=SEA;u4=20130923%20%7C%2020130926;ord=5360319407191.128?
I only need a few sections to verify that the doubleclick tag sent to google is correct. Using The regex shown above I have something like this.
Function CheckU3(contents As String)
Dim regx As RegExp
Set regx = New RegExp
Dim matches, s
With regx
.Pattern = "u3=(.*?);"
.Global = False
If regx.Test(contents) Then
Set matches = regx.Execute(contents)
For Each Var In matches.Item(0).SubMatches
s = Var
Next
End If
CheckU3 = s
End With
End Function
I know this could be simplified a lot, but the point is that I'm pulling the submatches using parans to get specifically what I need.
精彩评论