Regular expressions (or REGEX) are search patterns that can be used to check if a string of text conforms to a given pattern and extract or replace strings of text that match a given pattern. Given their complexity, this article offers streamlined summaries and examples of their use in Excel.

The REGEX functions are available for people using Excel for Microsoft 365 on Windows or Mac, as well as those using Excel for the web.

A list of product codes in Excel.

How to Use REGEXTEST

This function tests whether a string of text matches a given pattern, returning TRUE or FALSE based on this test. This is a great way to test that your data follows a certain pattern.

where

A list of product codes in Excel, and a second column that tests whether the code is valid using the REGEXTEST function.

Example of How to Use REGEXTEST

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

A valid code contains:

I want to test that all the product codes match this structure.

When I press Enter to apply this formula to all rows in column B, the result reveals that only two of the codes are valid (TRUE).

An Excel spreadsheet containing a list of names and phone numbers in column A, and subsequent columns created where the information will be extracted through REGEXEXTRACT.

This example contains the use of characters like [ ] and { }. However, there aremany more characters (also known as tokens) that can also be used to determine the patternused to perform the test, some of which I’ll use in the examples below.

REGEXEXTRACT: Find Specific Pieces of Text

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

Becauseformatted Excel tablescan’t handlespilled arrays, if you intend to extract matches as an array in argumentf, make sure your data is plainly formatted.

An Excel spreadsheet containing a first name and surname extracted into cells B2 and C2 from cell A2 using REGEXEXTRACT.

Example of How to Use REGEXTRACT

In this example, I want to extract the clients' first names, last names, and phone numbers into three separate columns.

Let’s focus on the names first. In cell B2, I will type:

A series of first and last names have been extracted in Excel from column A to columns B and C using REGEXEXTRACT.

When I press Enter, Excel performs the extraction successfully and adds a faint blue line around cell C2 to remind me that it’s a spilled array.

With cell B2 selected, I can now use the fill handle in the bottom-right corner of the cell to duplicate this relative formula to the remaining rows of details.

An Excel spreadsheet containing numbers extracted from cells containing letters and numbers using REGEXEXTRACT.

Now, I need to use a similar REGEXTRACT formula to extract the clients' phone numbers. In cell D2, I will type:

Since there is only one instance of this pattern in each cell in column A, I don’t need to add any more arguments. Again, once I’ve checked that this formula produces the expected result, I can use the fill handle to duplicate it to the remaining cells in column D.

A list of names in column A in Excel, with the empty column B headed ‘Reversed format’.

There are other ways in Excel to extract data and achieve similar results, such as usingthe TEXTSPLIT functionorExcel’s Flash Fill tool.

Manipulate Data with REGEXREPLACE

This function takes text in a cell and creates a new version of that data in another cell. Even though the function is called REGEXREPLACE, it doesn’t actually replace the original text in its original location.

Example of How to Use REGEXREPLACE

Below, you can see a list of names in column A. My aim is to recreate these names in column B, but using the “Last name, First name” format, including the comma separating the names.

I haven’t addressed argumentskandlin the above formula because I want Excel to replace all occurrences (the default for argumentk), and I want the replacement to be case-sensitive (the default for argumentl).

An Excel spreadsheet containing two formats of each client’s name, with the second format generated through the REGEXREPLACE function.

Because I’m using a formatted table, when I press Enter, the formula will apply to the remaining cells in column B.

Regular expressions aren’t just for use in Excel. In fact, you canuse REGEX to automate other taskson your computer, like fixing copy-pasted PDF text, bulk-renaming downloaded files, formatting currency, stripping HTML tags, and more.