Regex Formula in Microsoft Excel

It is well known that Regular expressions are ubiquitous in the developer world as they are used to validate the various website input, as well as searching the different types of word patterns in large strings in the texts, and for many other uses as well.

And in Microsoft Excel, the Regular Expressions (VBA RegEx or simply the VBA Regex) are not much advertised neither more popular in action, it is because most of the users are good in using the simpleLEFT, RIGHT, MID, as well as the FIND functions for the manipulation of their string.

In this tutorial, we will be discussing the different concepts used in Microsoft Excel, which are as follows:

1. What do you understand by the term Regex in Microsoft Excel?
2. Does the Regex function exist in Microsoft Excel?
3. What are the Regex cheat sheets in Microsoft Excel?
4. What do you mean by Custom RegEx functions in Microsoft Excel?
5. What do you mean by Regex Match Function in Microsoft Excel?
6. What is meant by Regex Extract Function in Microsoft Excel?
7. What is meant by Regex Replace Function In Microsoft Excel?

What do you mean by the word Regex in Microsoft Excel?

In Microsoft Excel, aregular expression(akaregexorregexp) is the special encoded sequence of the characters that primarily defines a search pattern. And by using that pattern, we can effectively find the matching character combination in a given string or can validate data input.

And the regular expressions primarily have their own syntax which consists of unique characters, operators, as well as the constructs.

• For example: [0-5] that matches any individual digit ranging from 0 to 5.

A Regular expression is mainly used in various programming languages, including the Virtual Basic for Applications (VBA) and JavaScript. The 'latter' has a unique RegExp object, which one can utilize in order to create custom functions under their needs.

Does the Regex function exist in Microsoft Excel?

The answer to the above question is that: regrettably, there are no such inbuilt Regex functions which exist in Microsoft Excel, and if someone wants to make use of the regular expressions in their respective formulas, so they need to create their user-defined function (VBA or .NET based), or they can install third-party tools which are usually supporting the regexes as well.

What are the Regex cheat sheets in Microsoft Excel?

We all know that whether the given regex pattern is very simple or highly sophisticated, it is built with the help of common syntax. And we have mentioned a quick reference to the main Regex patterns that will help an individual to get a grasp of the basics. It may also work as our cheat sheet when studying further examples respectively;

And if in case we are comfortable with regular expressions, we can jump straight to the RegExp functions as well:

Characters

Microsoft Excel uses these most frequently used patterns to match certain characters.

Character classes

With the help of these patterns, we can easily match the various elements of different character sets in an excel sheet respectively:

Quantifiers

In Microsoft Excel, the Quantifiers are termed to be the special expressions which are responsible for specifying the total number of characters to match; it should be applied to the character before it respectively.

Grouping

In Microsoft Excel, the Grouping constructs are primarily used to capture a substring from the given source string so we can easily perform some of the operations with it efficiently:

Anchors

In Microsoft Excel, the Anchors specify a position in the input string in which we have to look for a match.

Alternation (OR) construct

In Microsoft Excel, the alternation operand enables the OR logic so we can effectively match either this or that element respectively:

Look-around constructs

The Lookaround constructs in Excel are much helpful in action when we want to match something that is or is not followed as well as preceded by something else. And these expressions are sometimes called "zero-width assertions" it is because they effectively match a position rather than actual characters.

Note. In Virtual Basic for Application (VBA) RegEx flavour, look behinds are not supported.

What do you mean by Custom RegEx functions in Microsoft Excel?

From the above, we already know that Microsoft Excel has no built-in RegEx function, so to enable the regular expressions we all need to create three custom VBA (Virtual Basic for Application) functions (aka user-defined functions).

How does a VBA RegExp function work in Excel?

Now in this section, we will discuss the inner mechanics and exactly what happens at the backend.

So, when we start using the regular expressions in VBA (Virtual Basic for Application), we either need to activate the RegEx object reference library or we can use the CreateObject function efficiently.

The RegExp object is encapsulated with 4 properties which are as follows:

1. Pattern: It is used to match the particular type of the defined pattern in the given input string.
2. Global: Global is used for the purpose of controlling whether to find all matches in the given input string or just to find the first one.
3. Multiline: Multiline can be used to determine whether to match the given pattern across the line breaks in multi-line strings or only in the first line.
4. IgnoreCase: It defines whether a given regular expression is case-sensitive (default) or case-insensitive.

# Example: Microsoft Excel VBA Regex

Now in this example, we will check whether the mentioned pattern is present in the given text or not with the help of the RegEx. Test.

And we will be following the below steps to make use of the VBA RegEx.

Step 1: We need to define a new sub-procedure in order to create a macro.

Code:

Step 2: After that, we will define the two variables using RegEx as an Object that can be used to create a RegEx object and Str as a string effectively.

Code:

Step 3: In this step, we will create our RegEx object with the CreateObject function's help.

Code:

Step 4: Now, we will add the pattern to be tested with the RegEx function.

Code:

Step 5: After that, we will define the string within which we need to check the given pattern effectively.

Code:

Step 6: Then we will make use of the RegEx. Test to test whether the given pattern is present within the variable named str and debug it also.

Code:

Step 7: After that, we will hit the F5 or Run button from the keyboard to run the code in order to get the output as well.

What do you mean by Regex Match Function in Microsoft Excel?

It was well known that theRegExpMatchfunction is used to search an input string for the given text which matches a regular expression and then returns 'TRUE' if a match is found. Else it will return 'FALSE.'

SYNTAX:

In which;

• Text (required): Contains one or more strings to search in.
• Pattern (required): It is the regular expression to match.
• Match_case (optional: It primarily controls whether to match or ignore the text case in terms of TRUE OR FALSE.

# Example: How one can make use of the regular expressions to match the given strings

In the given dataset, we will assume that we want to identify the respective entries containing SKU codes.

And it was seen that each SKU starts with 2 capital letters, followed by a hyphen and 4 digits, so that we can match them with the help of the following expression.

In which:

• [A-Z]{2} means any 2 uppercase letters ranging from A to Z, and
• \d{4} means any 4 digits that range from 0 to 9.
• And a word boundary \b indicates that an SKU is a separate word and not part of a bigger string.

Now with the pattern which is established above, we will start typing a formula just like we normally do, and the respective function's name will get appear in the list that is suggested by Excel's AutoComplete effectively:

Supposing that the original string is present in A5, the formula goes as follows:

And for our convenience, we can input the given regular expression in a separate cell and make use of anAbsolute reference($A$2) for thepatternargument, as this ensures that the given cell address will remain unchanged when will copying out the formula to other cells:

And to display our text labels instead of displaying TRUE and FALSE, nest RegExpMatch in the IF function and specify the desired texts in thevalue_if_trueandvalue_if_falsearguments:

What is meant by Regex Extract Function in Microsoft Excel?

TheRegExpExtractfunction in Microsoft Excel searches for the substrings that match a regular expression and then extract all the matches or the specific match.

Formula:

In which,

• Text: It is the text string which needs to be searched.
• Pattern: Pattern is considered to be the regular expression to match.
• Instance_num: Instance_num is optional and primarily a serial number that indicates which instance needs to be extracted.
• Match_case: It primarily controls whether to match or ignore the text case in terms of TRUE OR FALSE.

# Example: How one can extract strings with the help of the regular expressions

In this example, we are discussing how to extract the invoice numbers and for this we will be making use of the simple regex function that is used to match out any 7-digit number:

Now we will be putting the pattern in the A2 cell, and we will get the job done with this compact and easy formula respectively:

If in case a pattern is matched, then a formula will extract an invoice number, and if in case no match is found, it will return nothing:

What is meant by the Regex Replace Function in Microsoft Excel?

A RegExpReplacefunction in Microsoft Excel is primarily used for the purpose of replacing out the values that are matching a regex with the text that we have specified.

In which:

• Text: It is the text string in the Excel sheet which needs to be searched.
• Pattern: Pattern is the required field and the regular expression to match.
• Replacement: Replacement is texts that can be used replace the matching substrings with it.
• Instance_num: the instance to replace. The default is "all matches".
• Match_case: It primarily controls whether to match or ignore the text case in terms of TRUE OR FALSE.

# Example: How one can replace or remove the given strings with the help of regexes

We all know that some of our records usually contain credit card numbers, which are very confidential. We can replace it with something or delete it altogether, and both tasks can be achieved with the help of theRegExpReplace function.

How? In a second scenario, we will be replaced with an empty string.

In our sample table, all the card numbers have 16 digits that are written in 4 groups and are separated with spaces, so to find them; we will replicate the pattern with the help of the regular expression:

And for the replacement, the following string can be used effectively:

And here is a complete formula that we can used to replacethe credit card numbers with the help of the insensitive information.

Moreover, with the regex as well as with the replacement text which are present in the separate cells A2 and B2, the formula works equally well:

In Microsoft Excel, "removing" is a particular case of "replacing", and in order toremove the credit card numbers, we can make use of an empty string ("") for thereplacementargument: