How to use the REGEX functions in Excel

In 2024, Microsoft added three new functions in Excel that rely on Regular Expressions or REGEX to identify and extract complex patterns from the provided data. These functions include the REGEXTEST, REGEXEXTRACT, and REGEXREPLACE functions, which are available in the web version of Excel and in Microsoft 365.

Using the REGEXTEST function

With the REGEXTEST function, you can find out whether your data contains a specific pattern and can show the output as TRUE or FALSE. The syntax for this function is REGEXTEST(a,b,c). Here 'a' refers to the value or text you want to test, while 'b' refers to the pattern used for the test, and 'c' is optional and can be zero when you need the test to be case-sensitive, or one otherwise. For instance, you can check whether a particular value, such as product codes or account codes contains any numbers.

1. In this example, we have an Excel sheet containing a few product/account codes.

2. To check whether the code contains any numbers, we click inside Cell C4 and type =REGEXTEST(A4,"[0-9]"

3. If the code contains any numbers, you should see the output as 'TRUE'.

4. Now, you can click at the edge of the cell where you entered the formula and drag it down to fill the rest of the cells and see the output automatically.

5. In the same way, you can use the REGEXTEST function for more complex codes, such as identifying telephone numbers in scraped data.

6. In this case, you can use the =REGEXTEST(A4,"\(?\d{3}\)?[-.\s]?\d{4}" function to determine whether the data contains any phone numbers.

7. Again, you can drag the formula down to check for all the values on the left.

Using the REGEXEXTRACT function

The REGEXEXTRACT function helps you find specific pieces of text in the provided data and has the REGEXEXTRACT(d,e,f,g) syntax. Here, 'd' is the cell reference or value from which you want to extract the required text and 'e' refers to the pattern to be extracted. You can use 'f' if you want to only find the first match, '1' if you want to find all possible matches, and '2' to find groups from the first match. You can also make the extraction case-sensitive with '0' or '1' if you don't want that.

1. We can use the previous example to extract phone numbers from the provided data.

2. We can use the  =REGEXEXTRACT(B6, "your_phone_number_pattern") function by typing =REGEXEXTRACT(A4,"(?\d{3})?[-.\s]?\d{3}[-.s]?\d{4}" in Cell C4.

3. If the reference cell contains a phone number, you will see it as the output. You can then drag the formula cell down to get the output for the other reference cells.

4. If you want to get all matches, you can modify the function as =REGEXEXTRACT(A4, "your_phone_number_pattern", 1). So, the function will be =REGEXEXTRACT(A4,"(?\d{3})?[-.\s]?\d{3}[-.s]?\d{4}",1.

Using the REGEXREPLACE function

The REGEXREPLACE function lets you find and replace a pattern with a different one. It has the syntax REGEXREPLACE(h,i,j,k,l), where 'h' refers to the reference cell or value to be replaced, 'i' is the pattern to be replaced, and 'j' is the replacement pattern. 'k' is optional and refers to how the pattern to be replaced occurs and 'l' can be zero if you want the replacement to be case-sensitive or one otherwise.

1. We can use the data from the previous example to try out the REGEXREPLACE function using=REGEXREPLACE(A4, "your_phone_number_pattern", "replacement_format").
2. Type =REGEXREPLACE(A4,"(?(\d{3}))?[-.\s]?(\d{3})[-.\s]?(\d{4})""$1-$2-$3")in Cell C4. This will standardize the phone number formats in your Excel sheet.

Things to know

• Characters like '{}' and '[]' are called tokens and are just two examples of the several tokens that you can use with the REGEX functions.
• To avoid running into issues when using REGEX functions, make sure your data is plainly formatted.
• You can also use conditional formatting with REGEX functions in Excel and can also enhance the data manipulation process.
• Excel also offers other ways by which you extract data, such as the Flash Fill tool and the TEXTSPLIT function.