My data 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.
The Code
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
Me.lstMatches.Clear
Me.lstSubMatches.Clear
If IsRegexPatternValid(Me.txtPattern) And Me.txtPattern <> "" Then
Set collMatches = GetRegexMatches(Me.txtInput, Me.txtPattern)
lstMatches.Clear
For i = 1 To collMatches.Count
Set OneMatch = collMatches(i)
lstMatches.AddItem OneMatch
If OneMatch.SubMatches.Count > 0 Then
For j = 1 To OneMatch.SubMatches.Count
Me.lstSubMatches.AddItem OneMatch.SubMatches(j - 1)
Next j
End If
Next i
End If
End Sub
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
With Regex
.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
collMatches.Add rgxMatch
On Error Resume Next
collUniqueMatches.Add rgxMatch, rgxMatch
On Error GoTo 0
Next rgxMatch
End If
End With
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.
Download
Here’s a workbook with the form and the code.
Excellent Tool!!!
Do you plan to extend it to look in a range of cells?
Thanks Orlando. I thought about doing that. However, I do mostly use it for grabbing an ID out of one cell at a time and then looking up that ID in the front end of a database to validate the info for that ID.
So I don’t know that I’ll be expanding it. It should just involve adding a For loop that goes through each cell in a range. If you do that and you want to share it or discuss feel free to post here or use the Contact form to talk to me.