Excel VBA find and replace regex

You can’t use regular expressions by writing an excel formula, but you can do it with a bit of VBA code. Follow these steps to add the proper reference in VBA and write a regular expression to find and replace data.

  1. Open an Excel file and then VBA Editor (Left Alt + F11).
  2. Choose Tools >> References.

  1. Add Microsoft VBScript Regular Expression 5.5. If you don’t do it, Excel won’t recognize the code and will return an error.
  2. Create a new module and add the following code.

Code explanation

The function takes 3 parameters:

rng – it’s a reference to a single cell,

reg_exp – regular expression,

replace – string you want to use, as a replacement to the regular expression value

IgnoreCase = False – it means that we are going to treat “This is text” as a different string that “this is text”, etc.

Global = True – the formula is not restricted to the first occurrence of the string.

Pattern = reg_exp – It’s going to use the regex from function’s parameter.

Copy the following data into your worksheet into cell A1.

This is text

123

qwqe

Hey! This is text

Hey! This is text!

this is text, indeed.

This is text or something else

Hey! This is text or something else

This is text 123

This is text 123abc

This is text This is text

Inside B1 enter the following formula.

It’s going to replace text after “This is text” only if the rest of the text consists of lowercase characters.

If we want to apply it also to uppercase characters, we have to change either IgnoreCase = True or use the following formula.

If we want to create a regular expression including all letters (lowercase and uppercase), and all numbers, we can use this formula.

This is the result.

If we want to replace any characters after the phrase we can use this formula.

In this example, the only difference will be in cell B5. This time instead of “Hey! This is text!” we have “Hey! This is text“.

Change IgnoreCase = True and we get one more change in cell B6. This time the text is “This is text” because it’s not changing letter casing.