Excel Find and Replace Regex: What Works and What Doesn’t

Excel Find and Replace Regex: What Works and What Doesn't

No: Excel’s Find and Replace dialog does not support regular expressions. In Microsoft 365, use the REGEXREPLACE worksheet function to make regex-based replacements in a formula, then copy the results back as values if you need to change the original cells.

What Excel Find and Replace supports

In Excel for Microsoft 365 on Windows, open the Replace tab with Ctrl+H, or choose Home > Find & Select > Replace. The dialog can find and replace literal text or numbers, and it can use Excel’s simpler wildcard system. It does not interpret regex syntax such as \d, \s, character classes, or groups.

That difference matters because symbols that have special meanings in a regular expression may be treated as ordinary characters in the dialog. For example, typing \d into Find what will not search for every digit. If you need to locate a pattern such as any sequence of digits, use a regex formula instead. Excel’s dialog also has search options for such things as matching case, searching within formulas or values, and searching the current sheet or workbook; those options do not turn its search field into a regex engine.

Excel does offer wildcards when you enable Use wildcards in the Replace dialog’s options. They are useful for straightforward searches, but they are not regular expressions:

* matches any number of characters. For example, North* can match text beginning with North.
? matches one character. For example, 202? can match a four-character value beginning with 202.
~ escapes a wildcard. Use ~* to search for a literal asterisk, or ~? for a literal question mark.

Wildcards cannot express many regex rules, such as “one or more digits followed by a hyphen and exactly two letters.” Treat the dialog’s wildcards and regex as separate tools, not interchangeable spellings of the same feature.

Use REGEXREPLACE for pattern-based changes

REGEXREPLACE applies a regular-expression pattern to text and returns the changed text in a cell. It does not edit the source cell directly. Put the formula in a helper column, review its results, and then decide whether to keep the formulas or replace the original data with values.

With phone numbers in A2:A20 and the first phone number in A2, enter this in B2 to remove everything except digits:

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

For a value such as (212) 555-0198, the result is 2125550198. The square brackets define a set of characters, and the caret at the start of that set means “anything except.” The pattern therefore matches each character that is not a digit; the empty replacement removes those matches. Fill the formula down to process the rest of the range.

With inconsistent spacing in A2:A20, enter this in B2 to replace each run of whitespace with one ordinary space:

=REGEXREPLACE(A2, "\s+", " ")

Here, \s represents whitespace and + means one or more of the preceding item. The formula can turn several spaces, or a tab, into one space. It does not remove leading or trailing spaces; if those are also a concern, check the results separately rather than assuming this pattern trims them.

With email-like text in A2:A20, enter this in B2 to replace the first occurrence of example.com with sample.org:

=REGEXREPLACE(A2, "example\.com", "sample.org", 1)

The backslash makes the period a literal period; an unescaped period in a regex usually means “any character.” The fourth argument selects an occurrence to replace. If you omit it, the function replaces all matches. The replacement text can also refer to captured groups, which is useful when a pattern identifies parts of a string that should be rearranged. Review the function’s current Excel help if you plan to build a complicated replacement around capture groups.

Regex functions and version availability

Excel’s regex functions are worksheet functions, not a regex mode in the Replace dialog. Microsoft 365 includes REGEXTEST for testing whether text matches a pattern, REGEXEXTRACT for extracting a match, and REGEXREPLACE for replacing matches. They use the PCRE2 regular-expression engine, so common constructs such as character classes, anchors, and quantifiers are available. A pattern must still follow that engine’s rules; regex syntax is not identical across every application.

These functions are documented for Microsoft 365, including supported desktop and web versions, and availability can depend on your update channel or organizational rollout. If Excel displays #NAME? for a regex function, check that Excel is updated and that the function is available for your account. Do not assume a perpetual-license version such as Excel 2021 has the same functions. To test availability without changing important data, enter a simple test in an empty cell: =REGEXTEST("abc", "^a"). If supported, it returns TRUE.

The optional case-sensitivity argument lets you control whether matching distinguishes uppercase from lowercase. For the regex functions, a value of 0 requests case-insensitive matching and 1 requests case-sensitive matching; check the individual function’s argument order before adding optional arguments. This differs from the Find and Replace dialog, where matching case is controlled through its search options.

How to apply replacements without losing data

A formula-based replacement is safer when you separate the transformation from the source data. A practical workflow is:

Save a copy of the workbook or duplicate the worksheet, especially before changing many records.
Add a helper column beside the source and enter the regex formula for the first row.
Fill the formula down, then inspect both ordinary and unusual examples, including blanks and values with punctuation.
If the output is correct, copy the helper results. Use Paste Special > Values to place fixed results over the original column, or leave the helper column as the cleaned output.
Keep the original data until you have checked totals, record counts, and any dependent formulas.

On Windows, Ctrl+H opens Find and Replace. On Mac, use the Home ribbon’s Find & Select > Replace command or the available Find menu command; ribbon labels can vary slightly with the Excel release. The regex worksheet functions work in supported Excel for Mac and Excel for the web as well, although the interface for copying, filling, and pasting values may differ. The dialog’s lack of regex support is not fixed by using Excel for the web or Mac.

Choosing between regex, wildcards, and ordinary replacement

Use the simplest tool that accurately describes the change. For a known phrase that should be replaced exactly, the Replace dialog is quick and easy to inspect. For a simple variable-length text search, its wildcards may be enough. Choose REGEXREPLACE when the replacement depends on a pattern, such as removing all non-digits, changing repeated separators, or matching only text at the start or end of a cell.

For a literal replacement inside a formula, Excel’s SUBSTITUTE function may be a better fit than regex. With product names in A2:A20 and the first name in A2, enter this formula in B2 to replace every occurrence of the exact text OldCo with NewCo:

=SUBSTITUTE(A2, "OldCo", "NewCo")

This changes the exact text, rather than interpreting a pattern. In contrast, the REPLACE function changes text at a specified character position; despite its name, it is not the regex replacement function. For example, it is useful when you know exactly which characters to overwrite, but it does not search for a regex pattern.

For a recurring import-cleaning process, Power Query may be worth considering because it can record repeatable data-transformation steps. Its standard text-cleaning tools are not a general regex-enabled version of Excel’s Replace dialog, however. Choose it for a refreshable sequence of supported transformations, not on the assumption that every transformation can use regex.

Common errors and important checks

Searching for regex in the wrong place: Ctrl+H opens the ordinary Replace dialog. Use a formula if the pattern requires regex.
Using the wrong slash: Excel formulas require straight double quotation marks around text arguments. Within a quoted formula string, a regex backslash is written as a backslash, not doubled as it often is in programming-language string literals.
Forgetting that a period is special: In regex, a period can match any character. Escape it as \. when you mean a literal full stop, as in a domain name.
Overwriting too soon: A formula may return unexpected results for blanks, unusual punctuation, or inconsistent input. Review the helper column before pasting over source data.
Ignoring case or occurrence settings: Decide whether uppercase and lowercase should count as the same, and whether the formula should replace one match or every match.
Assuming formatting is replaced: Regex formulas return text; they do not reproduce Find and Replace’s formatting controls or directly change cell formatting.

Also consider whether a cell contains a value or a formula. A regex formula processes the text you pass to it and returns a result; it does not rewrite the underlying formula in another cell. If the source cells contain numbers, dates, or formulas, verify how Excel presents those values as text before using a text pattern. A displayed date, for instance, may not be stored as the same text you see on screen.

Frequently Asked Questions

Can I use regex in Excel’s Ctrl+H Replace window?
No. The window supports ordinary find-and-replace and optional Excel wildcards, not regular expressions. Use REGEXREPLACE in a worksheet formula for regex-based changes.

How do I replace only digits or punctuation in Excel?
Use a regex formula in a helper column. For example, with the text to clean in A2, =REGEXREPLACE(A2, "[^0-9]", "") removes all non-digit characters. Check that deleting punctuation is appropriate for your data before applying it broadly.

Does REGEXREPLACE change the original cell?
No. It returns the transformed text in the cell containing the formula. To make fixed changes to the source column, review the output and paste the results as values over the original cells.

Why does Excel show #NAME? for REGEXREPLACE?
Your Excel version or update channel may not include the function, or its name may have been entered incorrectly. Check Microsoft 365 updates and function availability for your account; do not assume older perpetual-license editions support it.

Are Excel wildcards the same as regex?
No. Excel wildcards are limited to * for any number of characters and ? for one character, with ~ to escape them. Regex supports a much broader pattern language, but Excel’s Find and Replace dialog does not use it.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.