Zoho Sheet REGEXEXTRACT Function: First Matches, Arrays and Groups
Extract text with explicit return modes, complete structured-code examples and honest handling of an ambiguous official group example.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
REGEXEXTRACT returns text matching a regular expression. Its default mode selects the first match, while explicit modes can return all matches or the capturing groups of the first match. Extraction does not by itself turn a numeric-looking string into a number or certify that the rest of the input is valid.
Availability and verification
The official REGEXEXTRACT reference was retrieved October 15, 2026. Zoho’s release history announces REGEXMATCH, REGEXEXTRACT and REGEXREPLACE in January 2025. That establishes an announcement month, not a numbered first build or proof that every currently documented optional argument existed on that date. The last functioning version is unknown: current documentation is not a last-working-build test. The web entry does not establish mobile or offline-client parity.
The worked results and constraints below were independently checked. No native Zoho session was executed. Examples follow the help’s semicolon formula notation even where its syntax heading uses commas; verify the actual editor’s accepted separators. All datasets are fictional and fully specified rather than reconstructed from hidden embedded cells.
Syntax, return modes and case sensitivity
REGEXEXTRACT(text; regular_expression; [return_mode]; [case_sensitivity])
text supplies the string and regular_expression the pattern. return_mode defaults to 0, the first matching string. Mode 1 returns all matching strings as an array. Mode 2 returns the first match’s capturing groups as an array. Parentheses identify the capturing groups.
case_sensitivity defaults to 0, case-sensitive; 1 asks for a case-insensitive match. To set that fourth argument, supply the third argument explicitly, for example mode 0. The source does not name the regex engine or exhaustively describe group nesting, unmatched groups, empty matches or array orientation.
The logical sequences printed below describe expected returned elements, not a native-tested horizontal or vertical placement contract. Reserve clear output space for array results and verify their actual shape before wiring downstream references to particular cells.
Example 1: first-match extraction with visible inputs
Put these strings in A2:A5: Order 007 ready, Weight 46.59 kg, Item 12 then 345, and Batch 900. In B2 enter =REGEXEXTRACT(A2;"[0-9]+";0;0) and fill down.
Independent text expectations are 007, 46, 12 and 900. The digit pattern stops at the decimal point in the second row. To extract the decimal token there, use =REGEXEXTRACT(A3;"[0-9]+\.[0-9]+";0;0), with expected text 46.59.
The escaped dot means a literal decimal point. An unescaped dot has a broader regex meaning and should not be substituted casually. The number of decimal digits is not fixed by this particular pattern.
Example 2: distinguish first match from all matches
Use input Item 12 then 345 and 6. Mode 0 with "[0-9]+" returns the first text 12. Mode 1, =REGEXEXTRACT("Item 12 then 345 and 6";"[0-9]+";1;0), has independent element sequence 12, 345, 6.
Those are three separate extracted strings, not the single text “12, 345, 6”. A comma-separated list in this guide is a readable description of the array contents, not a promise that Zoho joins them with punctuation.
The all-match mode does not validate every other character in the source. Words and separators remain unmatched and are not represented in the returned sequence. Use a separate whole-string policy when the entire source must conform.
Advanced: split a structured code into explicit groups
For input INV-2026-0042, use =REGEXEXTRACT("INV-2026-0042";"^([A-Z]+)-([0-9]{4})-([0-9]{4})$";2;0).
The independent capturing-group sequence is INV, 2026, 0042. The hyphens and whole matched string are not additional named groups in this pattern. The final group preserves leading zeros because it represents identifier text.
This fixture uses one complete match with three simple groups. The source does not establish every behavior for nested groups or a mixture of optional participating and nonparticipating groups, so those cases are excluded from the example.
Advanced: compare case modes without changing source text
For Code ab-12, code CD-34, pattern "[A-Z]{2}-[0-9]{2}" in mode 0 returns CD-34 with case_sensitivity 0. With case_sensitivity 1 it returns ab-12, the earlier matching string.
Case-insensitive matching does not imply uppercase output. The extracted text retains the matched characters in this independent model. A downstream normalization step is separate from deciding what matches.
An inconsistency in the published examples
Zoho’s entry shows a parenthesized-letter pattern with capturing parentheses, omits return_mode, and prints the inner word rather than a clearly delimited whole match. That example does not align cleanly with the stated mode-0 whole-match contract and does not expose its source cell.
The guide therefore uses no-group patterns for default extraction and supplies mode 2 explicitly for group extraction. It does not infer undocumented default-group behavior or claim the ambiguous example was reproduced.
Errors and troubleshooting
The supplied error table refers to delimiters and instance counts that do not appear in this signature. It is not sufficient to establish the exact native no-match result, malformed-pattern error or invalid-return-mode behavior. All extraction fixtures deliberately contain a valid match.
If only part of a number is extracted, inspect the pattern rather than the display format. If the wrong occurrence is returned, check first-versus-all mode and case sensitivity. If an array appears to collide with other cells, preserve nearby data and inspect the output area; no exact collision error is claimed from a native run here.