Regular Expressions In Excel

• What is regex?
• A brief history of regex in Excel
• Regex vs. Excel wildcards
• Why regex is useful in Excel
• The REGEXTEST function
• The REGEXEXTRACT function
• The REGEXREPLACE function
• Regex quick reference
• Important regex terminology
• Regex anchors
• Regex tips
• Summary

What is regex?

What is regex? Regex, short for Regular Expressions, is a powerful tool for pattern matching in text data. Using a combination of metacharacters, literal characters, character classes, and quantifiers, you can define complex search patterns to extract, validate, or manipulate text data. See examples below . The main benefit of regex in Excel is the ability to work with text very precisely without resorting to complicated formulas that are hard to understand and maintain. In Excel, Regex support comes primarily from the introduction of three brand-new functions:

• The REGEXTEST function
• The REGEXREPLACE function
• The REGEXEXTRACT function

In addition to the three dedicated functions above, XLOOKUP and XMATCH have also been upgraded to support regex. Plus, you can use the functions above inside other formulas to instantly upgrade their capabilities. For example, you can use REGEXTEST inside the IF function as the logical test, which “upgrades” IF to support regex. In this article, I’ll introduce Excel’s new regex functions and provide examples of how these functions are helpful. But first, let’s review how we got here.

Many existing guides to using Regular Expressions in Excel on the web are based on VBA or custom add-ins. However, in Excel 365, you don’t need to use VBA or add-ins to use Regular Expressions because regex support is built-in. This guide covers the native regex support added to Excel via the three regex functions listed above.

A brief history of regex in Excel

Why doesn’t Excel support Regex? This is one of those questions that has bothered Excel power users for many years. It’s been a topic of heated debates and the cause of many clunky, complicated formulas. Although regex is a standard feature in many programming languages, it was notably absent from Excel for most of its history. Here’s how Excel’s text processing capabilities evolved:

• Excel has always supported basic wildcards (* and ?), but these are primitive compared to regex patterns. Users had to rely on combining functions like LEFT, RIGHT, MID, FIND, SEARCH, and SUBSTITUTE for pattern matching, resulting in complex, hard-to-maintain formulas.
• Power users worked around these limitations using VBA, Power Query, or custom add-ins for regex support, but these tools require different skills and are not available to all users.
• In Excel 2013, Microsoft added some regex-like capability with the FILTERXML function, which uses XPath queries for pattern matching. Still, this function is not widely used or available in Excel for Mac.
• In 2022, Microsoft improved text handling with TEXTSPLIT, TEXTBEFORE, and TEXTAFTER functions. These functions make it much easier to split text at specific locations in an Excel formula. However, they do not support regex.
• In December 2024, Excel introduced three regex functions: REGEXTEST, REGEXREPLACE, and REGEXEXTRACT. These functions modernize Excel’s text-processing capabilities and bring it up to speed with other professional tools.

Now that Excel supports regex directly, many complicated formulas of the past can be drastically simplified.

Regex vs. Excel wildcards

Excel wildcards are like a toolbox with just two tools: * for “anything” and ? for “one thing.” Sure, you can find “apple*” or “?at,” but that’s about it. They’re the flip phone of pattern matching.

Regex is a different story. While wildcards are asking “Does this have an “a” followed by… stuff?”, regex is performing complex queries like “Any word that starts with a capital letter, contains exactly two numbers, and ends with x or y but not z”.

Want to match exactly three digits followed by optional whitespace and a hyphen? Try \d{3}\s*- Need to find an email address or validate a strong password? Regex has patterns for that. It can even “look ahead” in your text to match patterns only when they occur before something else.

You get the idea. Regex goes far beyond basic wildcards.

Why regex is useful in Excel

Before we get into the details, let’s look at a specific example of how regex can help simplify a formula. In the workbook below, the goal is to extract the numbers from the product codes in column B. With hundreds of functions available, you might think this is a simple problem in Excel, but it’s not! The problem is that the numbers vary in length, and their location in the product code also changes. There’s just no easy way to figure out where each number begins and ends. Instead, the formula below in cell D5 takes a “brute force” approach and simply removes all non-numeric characters. It looks like this:

=TEXTJOIN("",TRUE,IFERROR(MID(B5,SEQUENCE(LEN(B5)),1)+0,""))

It’s not exactly obvious what this formula is doing, right? You can find an explanation here . It’s pretty complicated, and I’m even cheating a bit because I’m assuming at least Excel 2021, which has the SEQUENCE function . In Excel 2019, things get uglier because we need to spin up our own number array with the volatile INDIRECT function and the ROW function:

=TEXTJOIN("",TRUE,IFERROR(MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)+0,""))

In Excel versions before 2019, the formula becomes even more complicated!

What about regex? Does it help in this case? Yes . Regex helps a lot! In the worksheet below, the new formula in cell D5 is based on the REGEXEXTRACT function. Here it is:

=REGEXEXTRACT(B5,"\d+")

Yep, that’s the whole formula. Basically, we are asking REGEXEXTRACT for a sequence of 1 or more numbers. You can see the results below. I think you’ll agree that this new formula is a lot simpler 🙂

Now that you have a taste of how regex can help simplify difficult formulas, let’s look more closely at the three new regex functions.

The REGEXTEST function

The Excel REGEXTEST function tests for a given regex pattern. The result from REGEXTEST is TRUE or FALSE. For example, =REGEXTEST(A1,"[0-9]") will return TRUE if cell A1 contains any numeric digit, and =REGEXTEST(A1,"[A-Z]") will return TRUE if A1 contains any uppercase letters. REGEXTEST opens up new possibilities for data validation and text analysis directly within Excel formulas. In the worksheet below, REGEXTEST is configured to test addresses for one of four US states: MN, MT, ND, and SD. The formula in D5, copied down, looks like this:

=REGEXTEST(B5,"\b(MN|MT|ND|SD)\b")

The pattern “\b(MN|MT|ND|SD)\b” matches MN, MT, ND, or SD. The ‘\b’ is a word boundary character. It will match a space and any punctuation that typically appears around a word. The ‘|’ creates OR logic.

Note: While you probably don’t need a TRUE or FALSE result in a case like this, you could use exactly the same formula above inside the FILTER function to list all addresses that contain MN, MT, ND, or SD.

The REGEXEXTRACT function

The REGEXEXTRACT function extracts specific information from a string based on a Regex pattern. It’s perfect for pulling out key pieces of data from messy text. For example, in the worksheet below, the goal is to extract telephone numbers in the format xxx-xxx-xxxx from the text strings in column B. The formula in cell D5, copied down, looks like this:

=REGEXEXTRACT(B5,"\d{3}-\d{3}-\d{4}")

This example gives you a sense of regex’s power and flexibility. This formula is looking for and extracting phone numbers that follow a pattern like this: “123-456-7890”. Let’s break down each part:

• ‘\d{3}’ looks for exactly 3 digits
• ‘-’ looks for a hyphen
• ‘\d{3}’ looks for 3 more digits
• ‘-’ looks for another hyphen
• ‘\d{4}’ looks for 4 more digits

So, if you have text that contains something like “My phone number is 555-123-4567”, this formula will pull out just “555-123-4567”.

The REGEXREPLACE function

The REGEXREPLACE function allows you to replace parts of a string that match a regex pattern with something else. It’s very useful for cleaning up or reformatting text. You can think of REGEXREPLACE as a much more powerful version of the simplistic SUBSTITUTE function . For example, in the workbook below, REGEXREPLACE is configured to remove all non-numeric characters from the telephone numbers in column B. The formula in cell D5, copied down, is:

=REGEXREPLACE(B5,"[^0-9]","")

The pattern “[^0-9]” will match any character that is not a number. Since the replacement pattern is an empty string (""), the result is that REGEXREPLACE effectively “strips” all non-numeric characters from the input, and the final result contains numbers only.

Regex quick reference

Regex relies on patterns to match specific text. The table below contains some simple regex patterns. These patterns can be combined to create very capable text-matching formulas.

Regex terminology

Because Regex is essentially a mini-language, it has its own vocabulary. Here is a list of some important terminology:

• Pattern - The actual sequence of characters that defines the regex. For example \d+ is a pattern for one or more digits.
• Literal - Characters in a regex pattern that match themselves. For example, in cat , the literals are c , a , and t .
• Metacharacter - Characters with special meanings in regex. For example, a period . matches any character, ^ matches the start of a string, $ matches the end of a string.
• Character Class - A set of characters enclosed in square brackets [] that matches any one of the characters inside. For example, [aeiou] matches any vowel.
• Quantifier - Specifies how many instances of a character, group, or character class must be present in the input for a match. For example, a* matches zero or more a’s, a+ matches one or more a’s, a{3} matches exactly three a’s.
• Escape Sequence - A way to handle metacharacters as literals by preceding them with a backslash \ . For example, . matches a literal period.
• Group - A part of a regex pattern enclosed in parentheses () that can be referred to later. For example, (abc) matches the exact sequence abc .
• Capturing Group - A part of a regex pattern enclosed in parentheses () that not only matches text but also ‘captures’ or remembers what was matched for later use. For example, in the pattern (\d{4})-(\d{2}) the first group captures 4 digits before the hyphen and the second captures 2 digits after the hyphen.
• Non-capturing Group - A group that matches text but doesn’t capture it, written as (?:…) . Useful for grouping without creating a reference. Like () , but doesn’t store the match.
• Alternation - The pipe | character is used to match one thing or another. For example, cat|dog matches cat or dog .
• Anchor - Special characters that match positions within a string rather than actual characters. For example, ^ matches the start of a string, $ matches the end of a string.
• Wildcard - The dot . character, which matches any single character except a newline character. For example, c.t matches cat , cot , cut , etc .
• Boundary - Special sequences that match positions between characters. For example, \b matches a word boundary, \B matches a non-word boundary.
• Greedy vs Lazy - By default, quantifiers are ‘greedy’ and match as much text as possible. Adding ? after a quantifier makes it ’lazy’ and matches as little as possible. For example, .* vs .*?

Regex anchors

By default, regex will match any substrings that match the pattern. For example, the pattern cat will match “cat”, “catapult”, “scatter”, “concatenate”, or “the top category” because “cat” appears as a substring in each text string. To match an entire string exactly (i.e., to match the exact text in a cell in Excel), we need to use regex anchors:

• ^ (caret): Matches the start of the string. For example, ^abc matches “abc123” but not “123abc”.
• $ (dollar sign): Matches the end of the string. For example, abc$ matches “123abc” but not “abc123”.

To match the entire contents of a cell exactly, add the ^ and the $ to a pattern. For example, the pattern ^abc$ will match “abc” but not “123abc456” or “abcd”.

Without these anchors, a regex pattern will match substrings that appear anywhere in the string, which may or may not meet your needs. For instance, the pattern “abc” will match “abc” in “123abc456” or “abcd”, but the pattern “^abc$” will only match “abc”.

Regex tips

Regex patterns can get complicated fast. Here are some general tips for creating and debugging regex patterns:

• Start small - Test the simplest version of your pattern first and work from there. If \d+ doesn’t work, \d{3}-\d{3} probably won’t either.
• Use REGEXTEST to validate your patterns against sample data. REGEXTEST returns TRUE or FALSE, so it is perfect for testing in Excel.
• Special characters (. * + ? etc.) need to be escaped with ‘' to match these characters literally.
• Regular expressions are case-sensitive by default. You can disable this by setting case_sensitivity to 1 for each of the regex functions in Excel.
• ^ and $ match start/end of the entire string, not individual lines
• If a pattern isn’t matching, try making it more permissive. For example, \s* instead of just a space to handle variable whitespace.
• Seek help from AI like ChatGPT or Claude , and classic regex websites like regex101 and regexr . These are great resources to help you create the patterns you need.

Summary

The introduction of regex as a native tool in Excel formulas is a game-changer. Many complicated formulas of the past will slowly disappear as people learn how to turn “spaghetti code” into elegant and reliable formulas based on regex. Yet, regex definitely has a learning curve, and the large number of symbols and patterns can be intimidating. Regex is sometimes called a write-only language, only half jokingly :) However, you don’t need to master regex in order to use regex. A little goes a long way. Start with small problems and learn as you go.

Introduction

The complex number system is an extension of real numbers. Historically, the invention of complex numbers allowed mathematicians to find the roots of previously unsolvable polynomials. Nowadays, engineers use complex numbers to solve problems related to electronics, signal processing, and fluid dynamics. Famous equations like Euler’s Identity, Maxwell’s equations, and Schrödinger’s equation in quantum mechanics are examples of where the number system is used.

Excel supports complex numbers with functions that allow users to perform addition, multiplication, exponentiation, and other operations. The way the formula engine implements complex numbers is an example of functional programming. While complex functions have existed for years, Excel has recently evolved to provide more support for this paradigm with functions like LET , LAMBDA , REDUCE , and MAP . These new functions make it easier to use complex numbers in Excel without needing helper columns or array formulas.

Even if you don’t plan to use complex numbers in Excel anytime soon, they are an interesting example of how a functional programming approach can solve many problems. This article gives an overview of complex numbers and their operations in Excel and provides an example that uses some of the new functions.

What is a complex number?

A complex number is represented as “x+yi”, where x and y are real numbers, and i is the imaginary unit satisfying the equation i 2 =-1 . For example, the complex number “4+3i” has a real part of 4 and an imaginary part of 3. In Excel, we enter this complex number in a cell like this:

="4+3i" // the complex number z

We draw this complex number as an arrow in the complex plane.

In Excel, complex numbers are passed to functions as strings containing the suffix “i” or “j”. For example, to add two complex numbers together, we pass them to the IMSUM function like this.

=IMSUM("2+3i", "5+7i") // returns "7+10i"

You can also construct a complex number using the COMPLEX function, which avoids dealing with strings altogether.

=COMPLEX(4,3) // returns "4+3i"

To get the real part of the complex number, use the IMREAL function.

=IMREAL(COMPLEX(4,3)) // returns 4

Use the IMAGINARY function to get the imaginary part of the complex number.

=IMAGINARY(COMPLEX(4,3)) // returns 3

Conceptually, the complex number system is a two-dimensional number system that describes rotations. In math, the property i² = -1 defines the number system algebraically and is how the number system encodes rotations.

Of course, Excel handles this logic for us, but knowing this property is useful because it’s how you calculate the result of an operation with complex numbers by hand. For example, to multiply the complex numbers “i” and “4+3i” together, you apply this property to calculate the product.

<img loading=“lazy” src=“https://exceljet.net/sites/default/files/images/articles/inline/RotateByICalculation.png" onerror=“this.onerror=null;this.src=‘https://blogger.googleusercontent.com/img/a/AVvXsEhe7F7TRXHtjiKvHb5vS7DmnxvpHiDyoYyYvm1nHB3Qp2_w3BnM6A2eq4v7FYxCC9bfZt3a9vIMtAYEKUiaDQbHMg-ViyGmRIj39MLp0bGFfgfYw1Dc9q_H-T0wiTm3l0Uq42dETrN9eC8aGJ9_IORZsxST1AcLR7np1koOfcc7tnHa4S8Mwz_xD9d0=s16000';" alt=“Rotate by “i” calculation. - 8”>

We perform this same multiplication in Excel using a function call.

=IMPRODUCT("i", "4+3i") // returns -3+4i

Visually, multiplying a complex number like “4+3i” by “i” rotates the number 90 degrees counterclockwise around the origin.

<img loading=“lazy” src=“https://exceljet.net/sites/default/files/images/articles/inline/RotateByI.png" onerror=“this.onerror=null;this.src=‘https://blogger.googleusercontent.com/img/a/AVvXsEhe7F7TRXHtjiKvHb5vS7DmnxvpHiDyoYyYvm1nHB3Qp2_w3BnM6A2eq4v7FYxCC9bfZt3a9vIMtAYEKUiaDQbHMg-ViyGmRIj39MLp0bGFfgfYw1Dc9q_H-T0wiTm3l0Uq42dETrN9eC8aGJ9_IORZsxST1AcLR7np1koOfcc7tnHa4S8Mwz_xD9d0=s16000';" alt=“Rotate by “i”. - 9”>

As shown in this basic example, complex numbers describe rotations. Because of this, a complex number is often characterized by its radius and angle. The radius of the complex number “4+3i” we’ve been working with so far is 5, and the angle is 0.643501109 radians (approximately 37 degrees).

<img loading=“lazy” src=“https://exceljet.net/sites/default/files/images/articles/inline/ComplexNumberRadiusAndAngle.png" onerror=“this.onerror=null;this.src=‘https://blogger.googleusercontent.com/img/a/AVvXsEhe7F7TRXHtjiKvHb5vS7DmnxvpHiDyoYyYvm1nHB3Qp2_w3BnM6A2eq4v7FYxCC9bfZt3a9vIMtAYEKUiaDQbHMg-ViyGmRIj39MLp0bGFfgfYw1Dc9q_H-T0wiTm3l0Uq42dETrN9eC8aGJ9_IORZsxST1AcLR7np1koOfcc7tnHa4S8Mwz_xD9d0=s16000';" alt=“Radius and angle of “4+3i”. - 10”>

The angle of a complex number is measured in radians from the positive real axis, where counterclockwise is positive. The angle is also referred to as the phase of the complex number. To get the angle of the complex number, use the IMARGUMENT function.

=IMARGUMENT(COMPLEX(4,3)) // returns 0.643501109

To get the magnitude or length of the complex number, use the IMABS function.

=IMABS(COMPLEX(4,3)) // returns 5

Complex Operations

To add two complex numbers together, use the IMSUM function. For example, to add the complex numbers “4+3i” and “2-5i” together, use the following formula.

=IMSUM(
    COMPLEX(4, 3),
    COMPLEX(2,-5)
) // returns "6-2i"

The sum of two complex numbers is drawn by arranging the arrows tip-to-tail and drawing an arrow from the origin to the tip of the second number. This arrow is equal to the sum of the complex numbers.

Use the IMPRODUCT function to multiply complex numbers together. For example, use the following formula to multiply the complex numbers “1+2i” and “3+5i” together.

=IMPRODUCT(
    COMPLEX(1, 2),
    COMPLEX(3, 5)
) // returns -7+11i

We can visualize the product of the two numbers by transforming the coordinate system (shown in blue) so that the one (the green arrow) goes to the number “3+5i” in the complex plane. When we draw the transformed position of the other number, “1+2i”, its tip sits at the result of the multiplication.

The IMPRODUCT page discusses this in more detail. The point is Excel has a whole suite of functions for performing complex operations in addition to IMSUM and IMPRODUCT , such as IMSUB , IMDIV , IMSQRT , and IMPOWER . Each function corresponds to an operation that you perform with complex numbers.

Complex Functions in Excel

Below is a table of the functions we’ve discussed so far. It also contains one function we haven’t discussed yet, which is perhaps the most important function to know about complex numbers. The full list of 20+ complex functions is documented in the Engineering section of our functions reference here .

In practice, using complex numbers in Excel involves translating math into a series of complex function calls. Nothing is a better example of this than IMEXP or the complex exponential function.

The Complex Exponential Function

Anytime the letter e is raised to a power containing the complex constant “i” in equations and formulas, we express this in Excel using the IMEXP function. For example, the formula for a discrete Fourier transform looks like this.

We’ll discuss the specifics of this example later. For now, know that raising e to a complex number “z = x + yi” is the same as passing the complex number as input to the IMEXP function in Excel.

=IMEXP(z) // we write e^z like this

Complex numbers typically appear with the exponential function. Quite famously, the output of this function for complex input is defined by the trigonometric functions sine and cosine.

=IMEXP("θi") // returns cos(θ) + i sin(θ)

This formula is called Euler’s Formula . One way to quickly understand why it is so useful is to use it to rotate a complex number in the complex plane. For example, to rotate “4 + 3i” around the origin by 270 degrees, multiply the complex number by the value produced by the formula and the angle.

=IMPRODUCT(
  COMPLEX(4, 3),
  IMEXP(COMPLEX(0, RADIANS(270)))
) // returns "3-4i"

Visually, this corresponds to rotating the number counterclockwise around the origin by the angle.

Signal Processing Example

Complex numbers appear in contexts involving rotations, oscillations, and wave-like phenomena. In practice, we translate the math that describes these applications into a bunch of function calls to calculate results using complex numbers. This means using a function like IMSUM instead of the plus (+) operator to describe addition. Recently, Excel’s formula engine has been changing to make these calculations easier without helper columns. New functions like LET , LAMBDA , MAP , and SEQUENCE allow for a more functional programming approach when calculating results.

To be clear, using complex numbers in Excel is already an example of functional programming. These new functions just make the intermediate calculations easier to perform. A good example is the formula for a Discrete Fourier Transform.

This formula is the same version as the one you’ll find on the Discrete Fourier Transform Wikipedia page . We’ll also use the small array of sample data and expected output that someone has provided on the Wikipedia page.

Given an array of sample data, this formula returns a complex number for k, where k is a number that starts at zero and goes to the sample size minus one. This formula has a lot going on, so let’s break it down. Inside the summation, we multiply an element of the sample array by e raised to some complex power, including π and some variables related to the sample data.

In Excel, we calculate this intermediate value using the IMPRODUCT function and the IMEXP function.

IMPRODUCT(
    INDEX(sampleArray, n),
    IMEXP(COMPLEX(0, -2 * PI() * k * (n - 1) / m))
)

These intermediate values are wrapped in the summation operator, meaning we want to sum together the values for each value of n, ranging from 0 to the size of the sample array minus one. This is where the new functions come in handy. We can wrap the expression that calculates an intermediate value in a LAMBDA function that takes in an index of the sample array. This function is passed to MAP along with an array of indices to calculate all of the intermediate complex values, which we sum together with IMSUM .

=LET(
    sampleArray, B3:B6,
    m, ROWS(sampleArray),
    k, 0,
    IMSUM(
        MAP(
            SEQUENCE(m),
            LAMBDA(n,
                IMPRODUCT(
                    INDEX(sampleArray, n),
                    IMEXP(COMPLEX(0, -2 * PI() * k * (n - 1) / m))
                )
            )
        )
    )
)

This formula calculates the sum for k=0, which, with our sample data, we expect to be 2. Note that m has been substituted for N because Excel does not distinguish the lowercase n from the capital N in the formula.

Now, you could use a helper column to provide values for k and copy this formula down to calculate the results of the discrete Fourier transform, but let’s generate and pass in the values of k ourselves. This involves wrapping the previous formula in a lambda that takes k as input and passing that lambda to the MAP function. Here is the full formula, which “spills” the results in the cell’s column.

=LET(
    sampleArray, B3:B6,
    m, ROWS( sampleArray),
    kValues, SEQUENCE(m, 1, 0, 1),
    x_k, LAMBDA(k,
        IMSUM(
            MAP(
                SEQUENCE(m),
                LAMBDA(n,
                    IMPRODUCT(
                        INDEX(sampleArray, n),
                        IMEXP(COMPLEX(0, -2 * PI() * k * (n - 1) / m))
                    )
                )
            )
        )
    ),
    MAP(kValues, x_k)
)

This is the result when you enter the formula in Excel.

There are a couple of things to note about calculating the results of a Discrete Fourier Transform in Excel this way. The first is that due to the error in floating-point arithmetic, the actual results are slightly different from what we expected. For example, when we compare the numbers for k=2, we expect the result to be -2i when we calculated it to be -3.33066907387547E-15-2.00000000000001i. This number is really close to the value of -2i but not quite exactly equal to -2i.

The second thing to note is that there are faster algorithms than this formula to calculate the discrete Fourier transform. When testing this formula on sample arrays of less than 500 samples, it performs well. However, when testing this formula on an array of 1024 samples, it took about 5 seconds to calculate the results (results may vary depending on your computer specs). Excel actually provides an implementation of Fast Fourier Transform in the Analysis ToolPak , which is an add-in provided for free by Microsoft. This completes almost immediately for the same-sized sample array. When using the FFT algorithm, the size of the array must be a power of 2 and can be no larger than 4096 samples.

The existence of this add-in does not diminish the fact that we calculated a Discrete Fourier Transform in Excel using a functional programming approach. It’s a good example of how the new functions can be used to describe something quite complicated. It’s not hard to imagine someone implementing an FFT algorithm using this kind of approach, but that certainly is out of the scope of this article.

Have you solved an interesting problem with complex numbers in Excel? Let us know .