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

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

  1. Keep only the digits of a phone number

    ABC
    1Phone
    2(555) 123-45675551234567

    In C2: =REGEXREPLACE(A2, "\D", "") → 5551234567

    Try this example

  2. Swap "Last, First" to "First Last"

    ABC
    1Name
    2Lopez, AnaAna Lopez

    In C2: =REGEXREPLACE(A2, "^(\w+), (\w+)

    quot;, "$2 $1") → Ana Lopez

    Try this example

Common mistakes

Try REGEXREPLACE in the playground

Practise it

Related functions