Google Sheets 用 REGEXEXTRACT 从文本提取邮箱

Tested in: Live formula checks in Microsoft Excel 16.114.4 for Mac (English UI; system region zh-Hans_US), November 8, 2026; Google Sheets (spreadsheet locale zh_CN), November 9, 2026. Results and platform differences are noted below; this is not a certification of every menu workflow.

Which versions this applies to

Google Sheets on the web and the mobile apps. Google’s documentation says Sheets uses the RE2 regular expression engine, except for Unicode character class matching. Excel for Microsoft 365 now has its own REGEXEXTRACT too, with different arguments. See the Excel section at the end.

Example data

Paste this into column A, starting at A1:

What the quick-answer formula returns for each row:

Step 1: Extract the first email from one cell

Put this in B2 and drag it down:

=IFERROR(REGEXEXTRACT(A2, "[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}"), "")

Read the pattern in three parts:

Two details make this pattern more robust than it looks:

• Trailing punctuation is dropped. In row 4 the domain part first grabs Example.ORG.. The pattern still needs a dot followed by two or more letters at the end, so the regex engine backs off until .ORG satisfies it. The final full stop is left out.
• Fake addresses are skipped. In row 7, priya@site has no dot after it, so it fails the pattern. REGEXEXTRACT keeps scanning and returns the next valid address.

Without IFERROR, a cell with no match shows an error instead of a blank.

Step 2: Fill the whole column with one formula

Instead of dragging, put one formula in B2:

=ARRAYFORMULA(IF(A2:A="", "", IFERROR(REGEXEXTRACT(A2:A, "[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}"), "")))

The IF(A2:A="", "", …) part leaves empty rows empty, so your column doesn’t fill up with blanks you have to look at. Keep the cells under B2 clear. If anything is typed there, you’ll get the “would overwrite data” error: see Fix “Array Result Was Not Expanded Because It Would Overwrite Data” in Google Sheets.

Tip: store the pattern in a cell (for example E1) and reference it as REGEXEXTRACT(A2, $E$1). Then you only have one place to edit, and you don’t have to escape anything inside a long formula.

Step 3: Get every email when a cell has more than one

Google’s REGEXEXTRACT documentation says it “extracts the first matching substrings”. There’s no “global” option. To get all of them, flip the problem around: use REGEXREPLACE, which replaces all matches, to throw away everything that isn’t an email.

=IFERROR(SPLIT(TRIM(REGEXREPLACE(A2, "(?s)([A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,})|.", "$1 ")), " "), "")

How it works, from the inside out:

1. The pattern (email)|. matches either a whole email (captured as group 1) or any single other character.
2. The replacement $1 puts the email back followed by a space. For any other character, group 1 is empty, so the character becomes just a space.
3. (?s) lets . match line breaks too, which matters if the cell has text on several lines (RE2’s s flag).
4. TRIM collapses all those spaces into single spaces, and SPLIT breaks the result into one email per column.
5. IFERROR returns a blank when there’s no email, because SPLIT can’t split an empty string.

Row 3 now returns sales@acme.com in B3 and support@acme.com in C3. Leave enough empty columns to the right for the cell with the most emails.

Want them in one cell, comma-separated, instead? Replace the SPLIT step with SUBSTITUTE:

=SUBSTITUTE(TRIM(REGEXREPLACE(A2, "(?s)([A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,})|.", "$1 ")), " ", ", ")

Step 4: Extract the name, the domain or the username

Once you have the email in column B, the other pieces are short patterns. With one set of parentheses, REGEXEXTRACT returns only what’s inside them.

Username (before the @):

=IFERROR(REGEXEXTRACT(B2, "^([^@]+)@"), "")

Domain (after the @), for example to group contacts by company:

=IFERROR(LOWER(REGEXEXTRACT(B2, "@(.+)$")), "")

Display name in “Name ” text (row 2 returns Please contact Maya Chen):

=IFERROR(TRIM(REGEXEXTRACT(A2, "^(.*?)\s*<")), "")

.*? is a lazy quantifier, which RE2 supports. It stops at the first < instead of the last one.

Step 5: Clean and check the results

Lowercase everything so J.Smith+orders@Example.ORG and j.smith+orders@example.org don’t look like two different contacts:

=ARRAYFORMULA(IF(B2:B="", "", LOWER(B2:B)))

Get a unique list of addresses from the extracted column:

=UNIQUE(FILTER(LOWER(B2:B), B2:B<>""))

To spot duplicates instead of removing them, see Highlight Duplicates Across Two Columns in Google Sheets.

Check whether a cell holds only an email. Sheets has a built-in ISEMAIL function. Google says it checks whether the value “follows a commonly accepted format” but doesn’t verify that the address exists:

=ISEMAIL(B2)

To enforce your own pattern, anchor the regex with ^ and $ so the whole cell has to match:

=REGEXMATCH(B2, "^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$")

You can paste that last formula into Data > Data validation > Add rule > Custom formula is to reject bad entries as people type.

RE2 limits that break patterns copied from the internet

Many email regexes online are written for other engines. The RE2 syntax reference lists these as not supported, so a copied pattern that uses them won’t work in Sheets:

What RE2 does support and you’ll use often: \s, \d, [^…], lazy *? and +?, non-capturing groups (?:…), and inline flags like (?i) for case-insensitive matching and (?s) for multiline text.

Excel vs. Google Sheets

In Excel 365, the multi-email version is simply:

=REGEXEXTRACT(A2, "[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}", 1)

Microsoft documents REGEXEXTRACT for Excel for Microsoft 365 only. Older versions don’t have it.

Common mistakes

1. Adding parentheses for grouping. With a pattern like [\w.]+@[\w.]+\.(com|org), REGEXEXTRACT returns only the capture group: com. Google’s documentation says that with capture groups the function returns the groups, not the whole match. Use a non-capturing group (?:com|org) instead.
2. Running it on a number. Google says REGEXEXTRACT only works with text input. Phone numbers or IDs stored as numbers need converting first, for example REGEXEXTRACT(A2&"", …).
3. Forgetting to escape the dot. . means “any character”. Write \. for a literal dot before the top-level domain, or name@site,com will pass.
4. Expecting more than one result. REGEXEXTRACT stops at the first match. Use Step 3 when cells can contain lists.
5. Treating the pattern as a validator. The pattern catches typical addresses, not every address the email standards allow, and it can’t tell you whether a mailbox exists. Use it to extract, then check the results.

FAQ

How do I extract an email address from a cell in Google Sheets?

Use =REGEXEXTRACT(A2, “[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+.[A-Za-z]{2,}”) and wrap it in IFERROR to return a blank when a cell has no email.

Why does REGEXEXTRACT only return one email?

REGEXEXTRACT returns the first match only. To get all of them, replace everything that isn’t an email with REGEXREPLACE, then use SPLIT to put each address in its own column.

How do I extract the domain from an email address?

Use =REGEXEXTRACT(B2, ”@(.+)$”) on a cell that contains just the email. The parentheses tell REGEXEXTRACT to return only the part after the @.

Is REGEXEXTRACT case-sensitive?

Yes. The email pattern in this article lists both A-Z and a-z, so case doesn’t matter for it. For other patterns, add (?i) at the start to ignore case.

Why does my regex work on a regex website but not in Google Sheets?

Google Sheets uses the RE2 engine, which doesn’t support lookahead, lookbehind or backreferences. Patterns that use them return an error in Sheets. Rewrite them with capture groups.

Related tutorials

• Convert Numbers Stored as Text in Excel and Google Sheets (5 Methods) (cluster hub)
• Highlight Duplicates Across Two Columns in Google Sheets (Conditional Formatting)
• TRIM Not Working in Excel? Remove Non-Breaking Spaces (CHAR 160)
• Dependent Drop-Down List in Google Sheets That Works on Every Row

Live-test findings

For sample row 2, =IFERROR(TRIM(REGEXEXTRACT(A2,"^(.*?)\s*<")),"") returns Please contact Maya Chen, not just Maya Chen. This pattern extracts all text before the opening angle bracket; it does not infer which words form a person’s name. The email-address extraction examples passed in native Sheets.

Sources

• Google Docs Editors Help: REGEXEXTRACT (first match only, capture groups, RE2, text-only input): https://support.google.com/docs/answer/3098244
• Google Docs Editors Help: REGEXREPLACE (replaces all matching instances, RE2): https://support.google.com/docs/answer/3098245
• Google Docs Editors Help: ISEMAIL (checks format, doesn’t verify the address exists): https://support.google.com/docs/answer/3256503
• RE2 syntax reference (unsupported lookarounds and possessive quantifiers, flags i and s, lazy quantifiers): https://github.com/google/re2/wiki/Syntax
• Microsoft Support: REGEXEXTRACT function in Excel (return_mode, case_sensitivity, PCRE2): https://support.microsoft.com/en-us/office/regexextract-function-4b96c140-9205-4b6e-9fbe-6aa9e783ff57