Spreadsheet Practice

How to extract several parts of text into columns with REGEXEXTRACT in Google Sheets

Google Sheets formula

=REGEXEXTRACT(A2,"^([A-Z]+)-(\d{4})-(\d+)$")

Put each part you want in its own brackets: =REGEXEXTRACT(A2,"^([A-Z]+)-(\d{4})-(\d+)$") returns one column per bracket.

Result on the example
one result per row in B2, filled down to B6
Excel
See below

See it work

The formula is already in B2, filled down to B6. Change any input and the result updates, or edit the formula to experiment.

How it works

When a pattern has several capture groups (the parts in brackets), Google Sheets returns each group in its own column: the letters, the 4-digit year and the number. The ^ and $ make sure the whole code has this shape; a code that doesn't returns #N/A instead of a wrong split.

=REGEXEXTRACT(A2,"^([A-Z]+)-(\d{4})-(\d+)$")

Result{1 row × 3 cols} "NW", "2026", "0142"

  1. A2"NW-2026-0142"

    The value in A2.

  2. REGEXEXTRACT(A2,"^([A-Z]+)-(\d{4})-(\d+)$"){1 row × 3 cols} "NW", "2026", "0142"

    REGEXEXTRACT: The first match, or the captured group(s). #N/A when nothing matches.

    • textA2"NW-2026-0142"
    • regular_expression"^([A-Z]+)-(\d{4})-(\d+)$"

In Excel

In Excel 365, REGEXEXTRACT returns the capture groups when its return mode is 2: =REGEXEXTRACT(A2,"^([A-Z]+)-(\d{4})-(\d+)$",2). When the parts are separated by one delimiter, =TEXTSPLIT(A2,"-") is simpler.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=SPLIT(A2,"-")NW, SE, MIDWEST…When the parts are simply separated by a dash, SPLIT does the same. SPLIT turns "2026" into a number, while REGEXEXTRACT keeps text.

Common mistakes

  • Every bracket becomes a column. For grouping you don't want returned, use a non-capturing group (?:…).
  • The parts come back as text, including "2026" and "0142". Wrap a column in VALUE to calculate with it.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Extract several parts of text into columns with REGEXEXTRACT

In B2, extract the region, year and number from the order code into columns B, C and D with one formula, and fill down to B6.

Questions

How do I return just one of the groups?

Use a pattern with one capture group: =REGEXEXTRACT(A2,"-(\d{4})-") returns the year.

How do I handle codes that don't match?

Wrap the formula in IFERROR(…,"") and filter for blanks to review them.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine.