Text

REGEXREPLACE Function in Excel

Replaces text that matches a regular expression pattern with replacement text.

Syntax

  • =REGEXREPLACE(text, pattern, replacement, [occurrence], [case_insensitive])

Arguments

  • text (required): Text to modify
  • pattern (required): Regular expression pattern to match
  • replacement (required): Text to insert for each match
  • occurrence (optional): Replace only the nth match (omit for all)
  • case_insensitive (optional): TRUE to ignore case (default FALSE)

Examples

  • =REGEXREPLACE("abc-123", "[^0-9]", "") - Keep digits only - Result: 123
  • =REGEXREPLACE(A2, " +", " ") - Collapse multiple spaces - Result: Normalized spacing
  • =REGEXREPLACE(A2, "-", "/") - Swap delimiter - Result: Text with slashes

Excel REGEXREPLACE function documentation

  • REGEXREPLACE substitutes text that matches a regex pattern with replacement text — regex-aware alternative to chained [SUBSTITUTE](/functions/substitute/) calls.
  • Syntax: =REGEXREPLACE(text, pattern, replacement, [occurrence], [case_insensitive]).
  • Use to normalize phone numbers, strip non-digits, rewrite date formats in bulk, or redact PII in sample exports.
  • Combine with [REGEXTEST](/functions/regextest/) to validate before replace, and [REGEXEXTRACT](/functions/regexextract/) to audit captures.

REGEXREPLACE arguments explained

  • text (required): source string or cell.
  • pattern (required): regex describing what to replace — use ^ $ anchors for whole-string rules.
  • replacement (required): insert text; use $1, $2 for back-references to capture groups in M365 regex engine.
  • occurrence (optional): replace only the nth match; omit to replace all matches.
  • case_insensitive (optional): TRUE ignores case when matching.

Worked REGEXREPLACE examples

  • Remove non-digits: =REGEXREPLACE(A2, "[^0-9]", "") leaves digits only for phone normalization.
  • Collapse spaces: =REGEXREPLACE(TRIM(A2), " +", " ") replaces multiple spaces with one.
  • Swap delimiter: =REGEXREPLACE(A2, "-", "/") when pattern is simple; SUBSTITUTE may suffice.
  • Redact emails: =REGEXREPLACE(A2, "[\\w.-]+@[\\w.-]+\\.[A-Za-z]{2,}", "[redacted]") for sample datasets.

REGEX family workflow

  • Step 1 — REGEXTEST to flag rows that fail a format rule.
  • Step 2 — REGEXEXTRACT to pull structured fields into helper columns.
  • Step 3 — REGEXREPLACE to standardize display values before VLOOKUP/XLOOKUP.
  • Read [convert text to number](/blog/excel-convert-text-to-number-green-triangle-fix/) when extracts must become numeric.

People also ask

  • REGEXREPLACE vs SUBSTITUTE? — SUBSTITUTE finds literal text; REGEXREPLACE uses pattern matching.
  • Can I replace only the first match? — Set occurrence to 1.
  • Does REGEXREPLACE work in Excel Online? — Requires M365 builds that ship regex functions; verify on your tenant.

Common errors

  • #VALUE! for invalid regex
  • #NAME? without regex support
  • Unexpected partial replacements

Use cases

  • Normalize phone numbers
  • Clean imported text
  • Redact sensitive values
  • Standardize delimiters

Frequently asked questions

  • How is REGEXREPLACE different from SUBSTITUTE? SUBSTITUTE replaces literal text. REGEXREPLACE uses pattern matching, so one formula can remove all non-digits or match flexible formats.
  • Can REGEXREPLACE replace only the first match? Yes. Pass occurrence=1 to replace only the first match; omit occurrence to replace all matches.
  • Does REGEXREPLACE support capture group replacements? In supported builds you can use $1, $2 in replacement text to refer to captured groups from the pattern.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use REGEXREPLACE

  • Normalize phone numbers — common Text scenario for REGEXREPLACE.
  • Clean imported text — common Text scenario for REGEXREPLACE.
  • Redact sensitive values — common Text scenario for REGEXREPLACE.
  • Standardize delimiters — common Text scenario for REGEXREPLACE.

REGEXREPLACE in the Text category

  • Browse all Text functions at /categories/text/ for related formulas.
  • REGEXREPLACE syntax: =REGEXREPLACE(text, pattern, replacement, [occurrence], [case_insensitive])
  • text (required): Text to modify
  • pattern (required): Regular expression pattern to match
  • replacement (required): Text to insert for each match
  • occurrence (optional): Replace only the nth match (omit for all)
  • Confirm REGEXREPLACE arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm REGEXREPLACE arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • REGEXTEST (/functions/regextest/): Returns TRUE if text matches a regular expression pattern, otherwise FALSE.
  • REGEXEXTRACT (/functions/regexextract/): Extracts text that matches a regular expression pattern, optionally from a capture group.
  • SUBSTITUTE (/functions/substitute/): Replaces occurrences of old text with new text in a string.
  • REPLACE (/functions/replace/): Replaces part of a text string with a different text string, based on the number of characters you specify.
  • TRIM (/functions/trim/): Removes extra spaces from text, leaving only single spaces between words.

Errors to watch for

  • #VALUE! for invalid regex — review causes on linked error pages in the directory.
  • #NAME? without regex support — review causes on linked error pages in the directory.
  • Unexpected partial replacements — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter REGEXREPLACE on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

REGEXREPLACE worked examples to copy

  • =REGEXREPLACE("abc-123", "[^0-9]", "") — Keep digits only. Expected result: 123.
  • =REGEXREPLACE(A2, " +", " ") — Collapse multiple spaces. Expected result: Normalized spacing.
  • =REGEXREPLACE(A2, "-", "/") — Swap delimiter. Expected result: Text with slashes.

REGEXREPLACE reference summary for crawlers and offline review

  • REGEXREPLACE belongs to the Text category in Excel. Replaces text that matches a regular expression pattern with replacement text.
  • Full syntax: =REGEXREPLACE(text, pattern, replacement, [occurrence], [case_insensitive]). Open /functions/regexreplace/ for parameters, FAQs, and related pages.
  • Common mistakes: #VALUE! for invalid regex; #NAME? without regex support; Unexpected partial replacements
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.