I’ve just recently had a task where I had to do table lookups by a unique Code.
It was a pretty special sort of Code though. The first character has special meaning and the last two characters have special meaning.
I had the option of creating a complicated mapping table, but then I recalled my unix days… Regular Expressions!
Regular Expressions are great. They provide syntax for advanced string pattern matching.
In this example I want to find the Code which starts with a digit and ends with the letter J followed by the number 9.
The text in cell A3 is the pattern which matches what I’m looking for. In this case, that pattern matches the Code 9SGJ9.
I could have used ^(S|8)YRUP$ to match 8YRUP (or SYRUP if it were in the list of Codes).
The pattern matching syntax is sophisticated. The documentation is really worth a look.
The formula in cell B3 is:
=INDEX(B6:B15, RegMatch($A$6:$A$15, $A$3))
RegMatch is a User Defined Function in VBA for handling the pattern matching.
Regular Expression functions are already provided by Microsoft’s VBScript library so I just wrote a wrapper around it and called that RegMatch.
You’ll need to reference “Microsoft VBScript Regular Expressions”.
On my machine it’s version 5.5, but a later version is available for download from Microsoft’s website.
Public Function RegMatch(Source As Range, Pattern As String, Optional IgnoreCase As Boolean = True, Optional MultiLine As Boolean = True) As Long
Dim rng As Range, i As Long, j As Long
reg.IgnoreCase = IgnoreCase
reg.MultiLine = MultiLine
reg.Pattern = Pattern
i = 0: j = 0
For Each rng In Source
i = i + 1
If reg.test(rng.Value) Then
j = i
RegMatch = j
As I’m writing this post I’ve been looking on the Internet for other examples.
A great newsgroup post by Harlan Grove offers similar wrappers around the scripting library.