is are of course always perfectly formed, so should I ever need to do something as mundane as copy and paste one of them – a datum that is – into another application I would just select the entire cell and copy and paste it. I’d never need something like a Regex Cell Searcher, aka reg(Excel)lsearcher, to parse my datum, er data, no … datum. Whatever.
But say I did. Say I had wanted to select a cell and instantly have all the phone numbers in that cell plunked into a listbox.
And imagine I could type regexes really fast:
And let’s also say I wanted to pluck the area codes from the same cell using regex submatches:
And say – just daring to dream here – I wanted the first phone number in the listbox automatically copied to the Windows clipboard. Like below, where I click the cell, the matches are found, and the first one is ready to be pasted into a search engine:
The Regular Expression
Here’s the regex I came up with to check for a phone number, which I’m defining as three numbers, bracketed or not by parentheses, followed by a space, dash or period, followed by three numbers, etc.:
I can see some issues with it. Regexes are finicky things and accounting for all the possibilities is tricky.
Happily, I’m one of those people with a love of regular expressions. When I bought a book recently for the first time in perhaps 10 years, it was Mastering Regular Expressions.
Here’s the routine that fills the listbox:
Dim collMatches As Collection
Dim OneMatch As VBScript_RegExp_55.Match
Dim i As Long
Dim j As Long
If IsRegexPatternValid(Me.txtPattern) And Me.txtPattern <> "" Then
Set collMatches = GetRegexMatches(Me.txtInput, Me.txtPattern)
For i = 1 To collMatches.Count
Set OneMatch = collMatches(i)
If OneMatch.SubMatches.Count > 0 Then
For j = 1 To OneMatch.SubMatches.Count
Me.lstSubMatches.AddItem OneMatch.SubMatches(j - 1)
And here’s the code that uses regular expressions to get the matches and submatches:
Optional boolIgnoreCase As Boolean = True, Optional boolGlobal As Boolean = True, Optional boolMultiline As Boolean = True, _
Optional UniqueMatches As Boolean = False) As Collection
Dim Regex As VBScript_RegExp_55.RegExp
Dim rgxMatch As VBScript_RegExp_55.Match
Dim rgxMatches As VBScript_RegExp_55.MatchCollection
Dim collMatches As Collection
Dim collUniqueMatches As Collection
Set Regex = New VBScript_RegExp_55.RegExp
.Pattern = SearchPattern
.IgnoreCase = boolIgnoreCase
'Find all matches, not just the first
.Global = boolGlobal
'^ and $ work per-line, not just at begin and end of file
.MultiLine = boolMultiline
Set collMatches = New Collection
Set collUniqueMatches = New Collection
If .test(InputString) Then
'if matches, create a collection of them
Set rgxMatches = .Execute(InputString)
For Each rgxMatch In rgxMatches
On Error Resume Next
collUniqueMatches.Add rgxMatch, rgxMatch
On Error GoTo 0
In addition to the features mentioned above, you can click on any item in the Match or SubMatch listboxes and that item will be copied to the clipboard. The form uses an Application class to track selections across all workbooks and is resizable using my form resizing code.
Regex in Excel Proper?
For those of you visiting the Excel User Voice site, where you can suggest and vote on future features in Excel, you may have noticed a fairly popular suggestion would add regex capabilities to Excel.
Here’s a workbook with the form and the code.