RegEx UDF

Post date: Jan 31, 2017 10:22:27 PM

You may have heard of Regular Expressions. Regular 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],

[Global])

The Request Type can be either:

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.

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.

References:

• • RegEx Tutorial: http://www.regular-expressions.info/

  • RegEx Tester: http://regexr.com/