REGEXEXTRACT function

REGEXEXTRACT returns the portion of a text string that matches a regular expression, optionally respecting case and returning multiple matches.

=REGEXEXTRACT(text, pattern, [return_mode], [case_sensitivity])

Generate a REGEXEXTRACT formula

Describe what you need. The generator will reach for REGEXEXTRACT where REGEXEXTRACT is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How REGEXEXTRACT reads its arguments
textrequiredpatternrequiredreturn_modeoptionalcase_sensitivityoptionalREGEXEXTRACT →
ArgumentRequiredDescription
textRequiredString – the source text to search; if omitted or not a string, #VALUE! is returned.
patternRequiredString – a valid regular expression; an invalid pattern triggers #VALUE!.
return_modeOptionalOptional string – "first" (default) returns the first match, "global" returns all matches as an array; unsupported values cause #VALUE!.
case_sensitivityOptionalOptional Boolean – TRUE makes the match case-sensitive, FALSE (default) ignores case.

Returns

A text string or an array of text strings, depending on the return_mode.

Availability

Excel: 365 (2024+) · Google Sheets: Supported

Worked examples

1. Extract the first category that starts with “G”

CategoryMonthBudgetedActualVariance
RentJan120012000
GroceriesJan300350-50
UtilitiesJan15013020
EntertainmentJan100150-50
=REGEXEXTRACT(A2, "^G\\w+")

Result: Groceries

The formula looks at cell A2, which contains "Rent", but the pattern ^G\w+ seeks a word that begins with G. Because A2 does not match, the function evaluates the next cell in the column when dragged down. In row 3 (A3) the value is "Groceries", which satisfies the pattern, so REGEXEXTRACT returns that exact word.

2. Pull the month abbreviation from the Month column

CategoryMonthBudgetedActualVariance
RentJan120012000
GroceriesJan300350-50
UtilitiesJan15013020
EntertainmentJan100150-50
=REGEXEXTRACT(B2, "(Jan|Feb|Mar)")

Result: Jan

Column B holds the month name "Jan" for every row. The pattern (Jan|Feb|Mar) captures any of the three three-letter abbreviations. REGEXEXTRACT finds the first occurrence, which is "Jan", and returns it as a plain string.

3. List all numeric values from the Budgeted column

CategoryMonthBudgetedActualVariance
RentJan120012000
GroceriesJan300350-50
UtilitiesJan15013020
EntertainmentJan100150-50
=REGEXEXTRACT(C2:C5, "\\d+", "global", FALSE)

Result: 1200300150100

The range C2:C5 contains the budgeted amounts. The pattern \d+ matches one or more digits. With return_mode set to "global", REGEXEXTRACT extracts every numeric match across the range, producing an array of the four budgeted figures as text strings.

Common errors

Which REGEXEXTRACT error are you seeing?
REGEXEXTRACT returned an error#N/A
Adjust the pattern so it can be found in the text, or wrap the call in IFERROR to supply an alternate value.
#VALUE!
Correct the regular expression syntax; for example change "*invalid[" to "invalid\[".
#SPILL!
Clear enough empty cells to the right (or below) the formula so the array can spill, or switch to "first" mode.
ErrorWhy it happensHow to fix it
#N/AThe regular expression does not match any part of the supplied text.Adjust the pattern so it can be found in the text, or wrap the call in IFERROR to supply an alternate value.
#VALUE!The pattern argument contains invalid regex syntax, such as an unescaped bracket or stray quantifier.Correct the regular expression syntax; for example change "*invalid[" to "invalid\[".
#SPILL!Using return_mode "global" returns an array that cannot fit because adjacent cells are occupied.Clear enough empty cells to the right (or below) the formula so the array can spill, or switch to "first" mode.

Tips and when to use something else

  • Set case_sensitivity to TRUE when you need to distinguish between upper- and lower-case characters.
  • Combine REGEXEXTRACT with TEXTJOIN to concatenate multiple matches into a single string.
  • If you only need to test for a pattern’s presence, use REGEXMATCH instead of REGEXEXTRACT.
  • When extracting numbers for calculations, wrap REGEXEXTRACT with VALUE to convert the text result to a numeric type.

Related functions

REGEXMATCHTests whether a text string matches a regular expression pattern; returns TRUE for matches and FALSE for non-matches.TEXTJOINTEXTJOIN merges multiple text strings or ranges into one string, using a chosen delimiter and optional ignoring of empty cells.VALUEVALUE converts a text string that looks like a number, currency, or date into the underlying numeric value Excel or Sheets can use in calculations.IFERRORIFERROR returns the result of a formula unless that formula generates an error, in which case it returns a user-specified alternative value.ARRAYFORMULAARRAYFORMULA applies a single formula to an entire array or range, automatically filling results into multiple cells.

Frequently asked questions

Why does REGEXEXTRACT return #N/A instead of an empty string?
In both Excel and Google Sheets, REGEXEXTRACT signals that the pattern was not found with #N/A. This lets you differentiate between a genuine match that yields an empty string and a missing match. Use IFERROR to replace #N/A with a default value if needed.
Can REGEXEXTRACT extract multiple matches without spilling?
Only when return_mode is set to "global" does REGEXEXTRACT produce an array of all matches, which will spill into adjacent cells. To keep the result in a single cell, keep return_mode as "first" or wrap the array with INDEX to pick a specific element.
How do I make REGEXEXTRACT case-sensitive?
Pass TRUE as the fourth argument (case_sensitivity). For example, =REGEXEXTRACT(A2, "[A-Z]+", FALSE, TRUE) will only match uppercase sequences, ignoring lowercase characters.
What’s the difference between REGEXEXTRACT and REGEXREPLACE?
REGEXEXTRACT returns the portion of text that matches a pattern, while REGEXREPLACE substitutes the matched portion with new text. Use REGEXEXTRACT when you need to pull out data; use REGEXREPLACE when you need to modify or remove it.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17