If you do not see the tab, follow this URL to enable it.
https://msdn.microsoft.com/en-us/library/bb608625.aspx
2. VBA
To run Regex in Excel, you need to use VBA (Visual Basic for Applications).
From Developer tab, click Visual Basic icon.
3. Enable Regex for VBA for the workbook
In "Microsoft Visual Basic for Applications" window select "Tools" from the top menu.
Select "References".
Check the box next to "Microsoft VBScript Regular Expressions 5.5" to include in your workbook.
Click "OK".
Use Case:
We would like to parse specific patterns from range of cells in Excel, each cell could contain 0, 1 or more matches.
Here sample of data that user enter manually over times, to a text field without a good standard:
- Monitor 2X225,32C235, 21D2251
- 21A225; or 2C235; 21Z0251 keyboard
We need to capture and parse those ID, this looks like manual job, but with regex, we can parse this automatically. Regex should work with any new modern programming language, such as: Java, .Net and etc., but since Excel support VBA, we can make use of it.
If you notice above IDs, here is the possibility of patterns:
- 9X999
- 99X999
- 99X9999
With some testing with www.regex101.com, here is the RegEx to parse it:
[0-9]{2}[A-Z][0-9]{4}|[0-9]{2}[A-Z][0-9]{3}|[0-9][A-Z][0-9]{3}
From VBA window, copy and paste following script:
Private Sub splitUpRegexPattern()
Dim regEx As New RegExp
Dim strPattern As String
Dim MyRange As Range
Dim i As Integer
Dim strMatch As String
'Source Data
Set MyRange = ActiveSheet.Range("A2:A15")
For Each C In MyRange
' Regex pattern
strPattern = "[0-9]{2}[A-Z][0-9]{4}|[0-9]{2}[A-Z][0-9]{3}|[0-9][A-Z][0-9]{3}"
If strPattern <> "" Then
With regEx
.Global = True
.IgnoreCase = True
.Pattern = strPattern
End With
Set Matches = regEx.Execute(C.Value)
' Reset the variables
strMatch = ""
i = 1
' Iterate through the Matches collection
For Each Match In Matches
strMatch = "'" & Match.Value
' Display the matches at right columns
C.Offset(0, i) = strMatch
i = i + 1
Next
End If
Next
End Sub
Result:
The step and script is tested with Microsoft Excel 2013 and 2016.
