Excel : On the Fly Regex Filter

You have a column of text. Like a good Excel pro, it's part of a table. You want to have a column right next to this one that shows a Y in each cell for which the cell containing text of interest matches a regex.

How?

Easy. Say the Y (yes or no) column is Excel column H. It's header is cell H3. And you wish to put your regex in cell H2. You can do it as single quote followed by whatever, or whatever surrounded by double-quotes. The former does look prettier, but you want to stay flexible.

The right thing to do is to have the below VBA routine in your PERSONAL.xlsb (Personal Macro Workbook) (Get mine)

Then, in the topmost (see video below) cell-to-the-right, just put

=IF(PERSONAL.XLSB!RegexMatch(G4, $H$2), "Y", "")

And you're done. Having to use the qualified name is ugly, isn't it? :)

Public Function RegexMatch(text As String, pattern As String) As Boolean
    Dim re As Object
    
    If Left(pattern, 1) = """" And Right(pattern, 1) = """" Then
        pattern = Mid(pattern, 2, Len(pattern) - 2)
    End If
    
    Set re = CreateObject("VBScript.RegExp")
    re.Pattern = pattern
    re.IgnoreCase = True
    re.Global = False
    
    RegexMatch = re.Test(text)
End Function

---

Demo:

(Note use of custom shortcuts:

CTRL-ALT-V : fill in serial numbers in ascending order

CTRL-SHIFT-E : Exclude rows for which cell contents equal those in current cell

CTRL-ALT-F : Unfilter - show all)