Excel functions / Text
REGEXEXTRACT function
The part of the text that matches a regular expression: the first match, all of them, or its groups.
Syntax
=REGEXEXTRACT(text, pattern, [return_mode], [case_sensitivity])
Arguments
text: The text to search.pattern: The regular expression, such as "[0-9]+" for a run of digits.return_mode(optional): 0 (default): the first match. 1: every match, as a list. 2: the groups in brackets from the first match.case_sensitivity(optional): 0 (default): match case. 1: ignore case.
When to use it
REGEXEXTRACT finds the part of a text that matches a pattern, called a regular expression. The pattern describes the shape of what you want: [0-9]+ is a run of digits, [A-Z]{2} is two capital letters.
It does in one step what used to take MID, FIND and LEN together. It is in Excel for Microsoft 365.
Examples
The number inside some text
A B C 1 Note 2 Invoice 4471 paid 4471 3 Order AB-2093 sent In C2:
=REGEXEXTRACT(A2, "[0-9]+")→ 4471A code with a fixed shape
A B C 1 Note 2 Invoice 4471 paid AB-2093 3 Order AB-2093 sent In C2:
=REGEXEXTRACT(A3, "[A-Z]{2}-[0-9]{4}")→ AB-2093Two capital letters, a dash, then four digits.
Ignoring upper and lower case
A B C 1 Note 2 Invoice 4471 paid Invoice 4471 3 Order AB-2093 sent In C2:
=REGEXEXTRACT(A2, "invoice [0-9]+", 0, 1)→ Invoice 4471The last argument, 1, means ignore case. The 0 before it asks for the first match.
Common mistakes
- No match. When nothing fits the pattern the result is #N/A. Wrap it in IFNA to show something else:
=IFNA(REGEXEXTRACT(A2, "[0-9]+"), ""). - A number that will not add up. The result is always text, even when it is all digits. Turn it into a number with
=VALUE(REGEXEXTRACT(A2, "[0-9]+")). - Special characters. A dot, brackets,
+,*and?have meanings in a pattern. To match one literally, put a backslash in front:\.is a full stop.
Common questions
How do I extract numbers from text in Excel?
In Microsoft 365, use =REGEXEXTRACT(A2, "[0-9]+") for the first run of digits, and wrap it in VALUE to get a real number. In older versions, use Flash Fill or a combination of MID and FIND.
Try REGEXEXTRACT in the playground
Practise it
Related functions
- REGEXTEST: TRUE if any part of the text matches a regular expression.
- REGEXREPLACE: Replaces the parts of the text that match a regular expression.
- TEXTBEFORE: The text that comes before a delimiter.
- TEXTAFTER: The text that comes after a delimiter.
- MID: Characters from the middle of a text value.