Regex functions

A set of functions I’ve written to exploit the regular expression engine in vbscript, making the regular expression pattern-matching available on the Excel worksheet. Note that these all use early binding to VBScript_RegExp_55 in vbscript.dll. These are available for download here, bundled into an .xla file. Source code is as follows:

Public Function RXMATCH(ByVal find_pattern As Variant, _
ByVal within_range As Variant, _
Optional ByVal case_sensitive As Boolean = True) As Variant
' RXMATCH - Searches for the first cell matching regular expression pattern
' find_pattern in the range argument within_range.
' Optional boolean case_sensitive makes the regular expression case
' sensitive if true, insensitive otherwise. Default=true.
' within_range should be a single row or column range. If the range has
' multiple rows and columns, only the first row is searched.
Dim objRegex As VBScript_RegExp_55.RegExp
Dim lCell As Long ' Stores index of cell in array within_range
' Initialise Regexp
Set objRegex = New VBScript_RegExp_55.RegExp
With objRegex
.pattern = find_pattern
.Global = False
If Not case_sensitive Then
.IgnoreCase = False
Else: .IgnoreCase = True
End If
End With
' Initialise return value, assume no match
RXMATCH = CVErr(xlErrNA)
' Create array from range, then transpose to create column vector if
' necessary
If Not IsArray(within_range) Then ' range is a single cell
If objRegex.test(within_range) Then
RXMATCH = 1
Else
Exit Function
End If
Else
within_range = within_range.Value
If UBound(within_range, 2) > 1 Then _
within_range = WorksheetFunction.Transpose(within_range)
For lCell = LBound(within_range) To UBound(within_range)
If objRegex.test(within_range(lCell, 1)) Then
RXMATCH = lCell
Exit For
End If
Next lCell
End If
End Function

do you know if it is possible to utilize these regular expression functions in the (advanced/custom) filters in Excel lists (2003) or tables (2007)? Would be great if those filters could be specified as regular expressions instead of with Excels own limited wildcard system.

sorry no, not directly in the native Excel filter – at least not as far as I know. However you could use ISRXMATCH in a helper column on the table and filter on that returning TRUE. It would also be possible to write a regex filter in VBA. That was something I wished to include with the add-in, along with a regex version of the Find/Replace dialog – but I haven’t got around to it so far. Keep an eye out for the next version…

Thanks Tim, I’m really pleased you found these useful – and thanks for sharing the example! Along with many other things (including the find and replace dialog), I had planned to post some examples like this. Eventually. 🙂

Hi, I just need to do some searches in Excel using regular expressions and searching Google I’ve came accross your utility. Can you explain how to use it? I added it to my Excel as an add-in. How I use the functions? Their sytax?

Hi Horatio – your suggestion is something I’ve threatened to do in the past but never have. I’ll try to add some examples later today. In the meantime, if by searches you mean you want to find a cell within a row or column range matching a regular expression pattern, try =RXMATCH(pattern, range). This will return the position of the first matching cell within the range, in a similar way to the built-in function MATCH.

Feel free to email me directly (address at About Me) if you get stuck.