Posted on

RegEx VBA Sample Code

Once your add Reference “Microsoft VBscript Regular Expression 5.5”.

Following Function

simpleCellRegex function can be used to find string pattern:

any number 2 – 9 included;

word “session” included:

“(” included

“-” included

“:” included



Function simpleCellRegex(Myrange As Range) As String
Dim regEx As New RegExp
Dim strPattern As String
Dim strInput As String
Dim strReplace As String
Dim strOutput As String

strPattern = “^.*([2-9]|\bSession\b|[(]|[-]).*$”

If strPattern <> “” Then
strInput = Myrange.Value
strReplace = “”

With regEx
.Global = True
.MultiLine = True
.IgnoreCase = False
.Pattern = strPattern
End With

If regEx.Test(strInput) Then
‘simpleCellRegex = regEx.Replace(strInput, strReplace)
simpleCellRegex = “Dup”
simpleCellRegex = “Original”
End If
End If
End Function


Function NearMatch(vLookupValue, rng As Range, iNumChars)
Dim x As Integer
Dim sSub As String

Set rng = rng.Columns(1)
sSub = UCase(Left(vLookupValue, iNumChars))

For x = 1 To rng.Cells.Count
If UCase(Left(rng.Cells(x), iNumChars)) = sSub Then
NearMatch = rng.Cells(x).Address
Exit Function
End If
NearMatch = CVErr(xlErrNA)
End Function