This is not supposed to be a tutorial on Regular Expressions, just how to make use of them in Excel using VBA.
This is based on the fact that I have a sheet with large strings in column B and I want to extract relevant keys to column C.
I have highlighted the salient pieces of code in red.
Code:
Sub RegEx()
Dim RegEx As Object
Dim strTest As String
Dim valid As Boolean
Dim Matches As Object
Dim i As Integer
Set RegEx = CreateObject("VBScript.RegExp")
'What I happen to be looking for
RegEx.Pattern = "MT\d{6}V\d"
For i = 2 To 115
Range("B" & i).Activate
strTest = ActiveCell.Text
valid = RegEx.test(strTest)
If valid = True Then
Set Matches = RegEx.Execute(strTest)
Range("C" & i).Value = CStr(Matches(0))
Else
Range("C" & i).Value = "#N/A#"
End If
Next
Set RegEx = Nothing
End Sub