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.
- Open an Excel file and then VBA Editor (Left Alt + F11).
- Choose Tools >> References.
- Add Microsoft VBScript Regular Expression 5.5. If you don’t do it, Excel won’t recognize the code and will return an error.
- Create a new module and add the following code.
|
1 2 3 4 5 6 7 8 |
Function FindReplaceRegex(rng As Range, reg_exp As String, replace As String) Set myRegExp = New RegExp myRegExp.IgnoreCase = False myRegExp.Global = True myRegExp.Pattern = reg_exp FindReplaceRegex = myRegExp.replace(rng.Value, replace) End Function |
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.
|
1 |
=FindReplaceRegex(A1, "This is text.[a-z].*", "This is text") |
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.
|
1 |
=FindReplaceRegex(A1, "This is text.[a-zA-Z].*", "This is text") |
If we want to create a regular expression including all letters (lowercase and uppercase), and all numbers, we can use this formula.
|
1 |
=FindReplaceRegex(A1, "This is text.[a-zA-Z0-9].*", "This is text") |
This is the result.
If we want to replace any characters after the phrase we can use this formula.
|
1 |
=FindReplaceRegex(A1, "This is text.*", "This is text") |
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.