posted Jan 31, 2017, 2:22 PM by Craig Hatmaker

You may have heard of Regular ExpressionsRegular Expressions are insanely powerful and we can use their power in XL. We don't often need them, but when we do, there is nothing else that comes close. Unfortunately, RegEx is not an Excel function so I created a UDF (User Defined Function). It has the following syntax:

=RegEx( Cell or Value to Examine, 
              Pattern to Look For,
              Request Type,
              [Replace with String],
              [Ignore Case],

The Request Type can be either:

Type Name Description
0 Test Determine if the cell or value matches the Pattern to Look For
1 Replace Replace in the cell or value those characters matching Pattern to Look For with Replace with String
2 Extract Put into an array those characters in the cell or value matching Pattern to Look For

The Pattern to Look For can be quite simple or extremely complicated because it is so flexible. Here are some simple, and not so simple examples illustrating this function's power.

=RegEx( A1, "[0-9]", 0)Is there a number anywhere in A1?
=RegEx( A1, "7|8", 0)Is a 7 or an 8 anywhere in A1?
=RegEx( A1, "\d+", 2)Get all digits (numbers) from A1. NOTE: \d is the same as [0-9]
=RegEx( A1, "\D", 1, "")Remove (replace with nothing) all non-numeric characters from A1
{=RegEx( A1, "\w+", 2)}Put all words from A1 into an array. NOTE: This is an array formula 
=RegEx( A1, "\d{3}-\d{2}-\d{4}", 2)Get the social security number from A1
=RegEx( A1, " ", 1, "-")Replace all spaces in A1 with dashes 

As you can see, one UDF replaces many XL functions and can do what none of them can do individually. Below is the VBA for the RegEx UDF. It requires an enumeration (Enum) declared at the top of our module outside of any routines.