How to Use the REGEXREPLACE Function with Numbered Backreferences in Google Sheets

Data analysts and spreadsheet power users routinely encounter messy text strings—such as international telephone numbers, transposed dates, unformatted customer IDs, or inverted surname/firstname sequences. While standard string manipulation functions like SUBSTITUTE, LEFT, and MID can address basic replacements, they quickly become unwieldy when restructuring complex pattern-based strings. Google Sheets features native support for the REGEXREPLACE function, which accepts regular expression capture groups and numbered backreferences ($1, $2, $3) to reorder and transform text elements efficiently.

Understanding Capture Groups and Numbered Backreferences

Regular expressions identify substrings by matching character classes, quantifiers, and structural boundaries. Placing parentheses around specific sub-patterns creates an isolated capture group:

• Capturing sub-patterns: In the pattern "([A-Z]{2})-(d{4})", the expression captures two uppercase letters into Group 1 and four digits into Group 2.
• Numbered backreferences: In the replacement argument of REGEXREPLACE, backreferences access these matched segments using the dollar sign prefix followed by the group index (for example, "$1" references Group 1, while "$2" references Group 2).
• Positional reordering: By referencing capture groups in a different sequence—such as "$2-$1"—the function transposes components dynamically without requiring manual string concatenation.

Note that Google Sheets utilizes the RE2 regular expression engine. Backreferences in the replacement argument must use dollar notation ($1) rather than escape-slash syntax (\1), which is reserved for internal backreferences within the regex pattern itself.

REGEXREPLACE Syntax and Core Mechanics

The standard syntax for the function is structured as follows:

=REGEXREPLACE(text, regular_expression, replacement)

Each argument plays a distinct role in pattern evaluation:

• text: The source cell coordinate or text literal containing the unformatted string (e.g. cell A2).
• regular_expression: The RE2 pattern string containing one or more parenthesised capture groups.
• replacement: The destination template incorporating literals, delimiters, and numbered backreferences (e.g. "$2/$1/$3").

Practical Example 1: Transposing Date Formats from YYYY-MM-DD to DD/MM/YYYY

When importing international data feeds, dates frequently arrive formatted as ISO 8601 strings (e.g. 2026-03-15). To convert this text representation into standard UK date order (15/03/2026) using backreferences:

=REGEXREPLACE(A2, "(d{4})-(d{2})-(d{2})", "$3/$2/$1")

How the pattern executes:

1. (d{4}) captures four year digits into $1 ("2026").
2. (d{2}) captures two month digits into $2 ("03").
3. (d{2}) captures two day digits into $3 ("15").
4. The replacement string "$3/$2/$1" reconstructs the date in day-month-year sequence, inserting clean forward slashes between the backreferenced values.

Practical Example 2: Reordering “Lastname, Firstname” into “Firstname Lastname”

Human resources registries and customer tables often record names with surnames leading. To invert “Smith, John” into “John Smith”:

=REGEXREPLACE(A2, "^([^,]+),s*(.*)$", "$2 $1")

Here, Group 1 (([^,]+)) matches all characters preceding the comma, while Group 2 ((.*)) captures the first name following the whitespace. The backreference template "$2 $1" outputs the first name first, followed by a single space and the surname.

Practical Example 3: Standardising Unformatted 10-Digit Telephone Numbers

To format raw digit blocks (such as 02079460123) into readable phone numbers with parentheses and spacing:

=REGEXREPLACE(TEXT(A2, "00000000000"), "(d{4})(d{3})(d{4})", "($1) $2 $3")

This formula isolates dialing prefixes, exchange codes, and subscriber lines, returning (0207) 946 0123 cleanly.

Array Processing via ARRAYFORMULA

To apply REGEXREPLACE across an entire column of thousands of records without dragging down formulas manually, nest the function inside ARRAYFORMULA:

=ARRAYFORMULA(IF(ISBLANK(A2:A), "", REGEXREPLACE(A2:A, "(d{4})-(d{2})-(d{2})", "$3/$2/$1")))

The IF(ISBLANK()) guard ensures empty rows below your dataset remain blank, preventing calculation clutter while ensuring immediate automated parsing as new rows are appended.