How to run Regex in Microsoft Excel?

1. Show Developer tab in Excel ribbon
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.