Excel functions / Text
REGEXREPLACE function
Replaces the parts of the text that match a regular expression.
Syntax
=REGEXREPLACE(text, pattern, replacement, [occurrence], [case_sensitivity])
Arguments
text: The text to change.pattern: The regular expression, such as "[0-9]+" for a run of digits.replacement: What to put in place of each match. $1, $2… stand for the groups in brackets.occurrence(optional): Which match to replace: 0 (default) = all, 2 = the second, -1 = the last.case_sensitivity(optional): 0 (default): match case. 1: ignore case.
When to use it
REGEXREPLACE replaces the parts of a text that match a regular expression. Where SUBSTITUTE replaces one exact piece, REGEXREPLACE can strip every digit, collapse runs of spaces, or reorder parts with $1 and $2.
Examples
Keep only the digits of a phone number
A B C 1 Phone 2 (555) 123-4567 5551234567 In C2:
=REGEXREPLACE(A2, "\D", "")→ 5551234567Swap "Last, First" to "First Last"
A B C 1 Name 2 Lopez, Ana Ana Lopez In C2:
=REGEXREPLACE(A2, "^(\w+), (\w+)quot;, "$2 $1")→ Ana Lopez
Common mistakes
- Special characters. In a pattern, . ( ) [ ] + * ? and \ mean something. Put a \ in front to match them literally.
Try REGEXREPLACE in the playground
Practise it
Related functions
- REGEXTEST: TRUE if any part of the text matches a regular expression.
- REGEXEXTRACT: The part of the text that matches a regular expression: the first match, all of them, or its groups.
- SUBSTITUTE: Replaces specific text with new text.
- TRIM: Removes extra spaces, leaving single spaces between words.