Quick Navigation
REGEXREPLACE Function
Summary
The REGEXREPLACE function performs powerful text substitutions using regular expressions, enabling precise pattern matching and replacement within strings. This advanced text processing tool is essential for data cleaning, formatting standardization, and complex string manipulation tasks in Excel.
Syntax
REGEXREPLACE(text, pattern, replacement, [occurrence], [case_sensitivity])Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
| text | Text |
Yes | Text string or cell reference containing the text to search and replace within |
| pattern | Text |
Yes | Regular expression defining the pattern to match and replace |
| replacement | Text |
Yes | Replacement text, which can reference capturing groups as $1, $2, etc. |
| occurrence | Number |
No | Specific occurrence to replace (0=default=all, positive=from start, negative=from end) |
| case_sensitivity | Number |
No | 0=case sensitive (default), 1=case insensitive |
Using the REGEXREPLACE Function
REGEXREPLACE revolutionizes text processing in Excel by bringing full regular expression capabilities to spreadsheet formulas. Use it to standardize formats, anonymize data, restructure strings, or perform complex find-and-replace operations that traditional SUBSTITUTE functions cannot handle.
Common REGEXREPLACE Examples
Anonymize Phone Numbers
=REGEXREPLACE(A2,"[0-9]+-","***-")Replaces area codes in phone numbers like "(378) 555-4195" with "***-" to protect privacy while preserving format.
Reorder Name Parts
=REGEXREPLACE(A2,"([A-Z][a-z]+)([A-Z][a-z]+)","$2, $1")Converts "SoniaBrown" to "Brown, Sonia" using capturing groups to swap first and last names.
Remove All Digits
=REGEXREPLACE(A2,"[0-9]","")Strips all numeric characters from text, useful for cleaning product codes or identifiers.
Replace Second Email Domain
=REGEXREPLACE(A2,"(\w+)@(\w+\.\w+)","[email protected]",2)Targets only the second occurrence to replace specific email domains while leaving others intact.
Frequently Asked Questions
Common Errors and Solutions
#VALUE! error
Cause: Invalid regex pattern syntax
Solution: Verify pattern uses valid PCRE2 syntax. Test with REGEXTEST first.
No matches found
Cause: Pattern doesn't match any text
Solution: Check pattern logic and test with simpler patterns first
Unexpected replacement result
Cause: Incorrect capturing group references ($n)
Solution: Count parentheses carefully - $1 is first group, $2 is second, etc.
Case matching issues
Cause: Pattern expects different case than source text
Solution: Add case_sensitivity=1 for case-insensitive matching
Notes
- Always returns text results - wrap with VALUE() for numeric conversion
- PCRE2 supports advanced features: lookaheads/behinds, conditionals, Unicode
- Escape special regex characters in replacement text with \
- Common tokens: [0-9]=digits, [a-z]=lowercase letters, .=any char, *=0+ times, +=1+ times
- Test complex patterns with REGEXTEST before using in REGEXREPLACE
Compatibility
Available in: Excel 365, Excel 2021, Excel 2019
Not available in: Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003
Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 365, Excel 2021, Excel 2019