Quick look at Excel regular expression functions: REGEXTEST, REGEXEXTRACT and REGEXREPLACE

Regular expression (REGEX) is a search pattern used to check if a text string complies with a given pattern and to extract or replace a text string matching the given pattern. Given its complexity, this article provides a simplified summary and example of its use in Excel.

REGEX function is suitable for users who use Microsoft 365 Excel for Windows or Mac, as well as those who use Excel on the web.

REGEXTEST: Text Pattern Matching Test

This function tests whether the text string matches the given pattern and returns TRUE or FALSE based on the test results. This is a great way to test whether the data follows a specific pattern.

Grammar

<code>REGEXTEST(a, b, c)</code>

Of:

• a (Required) is the text, value, or cell reference to the text to be tested.
• b (Required) is the mode used to perform tests.
• c (Optional) 0 if you want the test to be case sensitive; otherwise 1.

REGEXTEST Example Use

This spreadsheet contains a list of product codes that must follow a strict structure.

Factory codes include:

• Lower case representation of product size ("xs" means very small, "s" means small, "m" means medium, etc.),
• Represents one or two digits of product material,
• Three capital letters representing the location of the product manufacturing, and
• The three parts are separated by short horizontal lines.

We need to test whether all product codes match this structure.

So, in cell B2, enter the following formula:

<code>=REGEXTEST([@Code],"[xs|s|m|l|xl]-[0-9]{1,2}-[A-Z]{3}",0)</code>

Of:

• [@Code] is a structured reference to the column containing the code to be tested.
• [xs|s|m|l|xl] is the first part of the product code to be tested, and the vertical line indicates "or".
• [0-9]{1,2} is the second part of the product code to be tested, [0-9] means any one digit, and {1,2} means there can be one or two digits.
• [A-Z]{3} is the third part of the product code to be tested, [A-Z] means any capital letters, and {3} means there must be exactly three capital letters.
• The three parts of the code to be tested are separated by short horizontal lines.
• 0 is the last parameter in the formula, which tells Excel to test case sensitivity.

After pressing Enter to apply this formula to all rows in column B, the result shows that only two codes are valid (TRUE).

• m-2-UK is invalid (indicated by FALSE results) because the country code contains only two capital letters.
• xl-714-AUS is invalid because the material code contains three digits.
• S-5-USA is invalid because the size code is capitalized.

This example contains the use of characters such as [] and {}. However, there are many other characters (also called markers) that can also be used to determine the pattern used to perform the test, some of which will be used in the examples below.

REGEXEXTRACT: Extract specific text fragments

This function returns part of the text in the cell according to the specified pattern. For example, you might want to separate numbers and text.

Grammar

<code>REGEXTEST(a, b, c)</code>

Of:

• d (Required) is the text, value, or cell reference from which the text is to be extracted.
• e (Required) is the pattern to be extracted.
• f (Optional) 0 if you want to extract only the first match; 1 if you want to extract all applicable matches as an array; Match extract group, then 2.
• g (Optional) 0 if you want the extraction to be case sensitive; otherwise 1.

Because the formatted Excel table cannot handle overflow arrays, if you plan to extract the match as an array in the parameter f, make sure your data is in normal format.

REGEXEXTRACT Example Use

In this example, the customer's name and phone number need to be extracted into three separate columns.

First focus on the name. In cell B2, enter the following formula:

<code>=REGEXTEST([@Code],"[xs|s|m|l|xl]-[0-9]{1,2}-[A-Z]{3}",0)</code>

Of:

• A2 is the cell containing the data to be extracted.
• [A-Z][a-z] Tell Excel to extract any word that starts with uppercase letters followed by lowercase letters, where " " means to return one or more lowercase letters in each pattern.
• 1 means you want to separate each example of the above pattern into individual cells as an array (in other words, the first name is in cell B2 and the second name is in cell C2). If this parameter is omitted, Excel returns only the first match (first name) in cell B2.

After pressing Enter, Excel performs the extraction successfully and adds a light blue line around cell C2 to remind you that it is an overflow array.

After selecting cell B2, you can now copy this relative formula to the remaining details rows using the fill handle in the lower right corner of the cell.

Now, a similar REGEXTRACT formula needs to be used to extract the customer's phone number. In cell D2, enter the following formula:

<code>REGEXEXTRACT(d, e, f, g)</code>

Of:

• A2 is the cell containing the data to be extracted.
• [0-9()] The numbers from zero to nine extract the numbers in parentheses, where " " extracts one or more numbers in this pattern.
• [0-9-] Extract the remaining numbers in the string. The second "-" represents a short horizontal line that separates the two parts of the phone number. " " tells Excel that if the string contains numbers, one or more of them are to be extracted. number.

Since there is only one instance of this pattern in each cell in column A, no additional parameters need to be added. Likewise, once you check that this formula produces the expected result, you can use the fill handle to copy it to the remaining cells in column D.

There are other ways in Excel that extract data and get similar results, such as using the TEXTSPLIT function or Excel's Quick Fill tool.

REGEXREPLACE: Operational data

This function takes the text in a cell and creates a new version of the data in another cell. Even if the function is called REGEXREPLACE, it does not actually replace the original text in its original position.

Grammar

<code>REGEXTEST(a, b, c)</code>

Of:

• h (Required) is the text, value, or cell reference to the text to be replaced.
• i (Required) is the mode to be replaced.
• j (Required) is the replacement content to be created.
• k (Optional) is the number of occurrences of the pattern to be replaced.
• l (Optional) 0 if you want the replacement to be case sensitive; otherwise 1.

REGEXREPLACE Example of usage

Below, you can see a series of names in column A. The goal is to recreate these names in column B, but use the "last name, first" format, including commas that separate names.

In cell B2, enter the following formula:

<code>=REGEXTEST([@Code],"[xs|s|m|l|xl]-[0-9]{1,2}-[A-Z]{3}",0)</code>

Of:

• [@Client name] Refers to the column containing the data to affect.
• [A-Z][a-z] Include twice in the formula (and separated by spaces), tell Excel to get two text strings containing uppercase letters followed by one or more lowercase letters.
• , Tell Excel to reverse the order of these two text strings and separate them with commas and spaces. If the dollar sign is not included, Excel will only return "2, 1" as the result for each cell.

The parameters k and l> are not processed in the above formula, because you want Excel to replace all occurrences (the default values ??of parameter k) and you want to replace Case sensitive (default value for parameter l).

Because the formatted table is used, the formula will be applied to the remaining cells in column B after pressing Enter.

Regular expressions are not only used in Excel. In fact, you can use REGEX to automatically perform other tasks on your computer, such as fixing copy-paste PDF text, batch renaming downloaded files, formatting currency, removing HTML tags, and more.

The above is the detailed content of How to Use the REGEX Functions in Excel. For more information, please follow other related articles on the PHP Chinese website!