Formulas to Extract Email Addresses in Google Sheets

If you have a column of messy text, notes, or imported data and need to pull out just the email addresses, Google Sheets has no built-in EXTRACT_EMAIL function. You are stuck combining REGEXEXTRACT with a pattern that matches the structure of an email, or you copy-paste into a tool and clean it manually. Neither is fast when you have hundreds of rows.

The common use case: you exported a CRM dump, scraped a contact list, or received a CSV where emails are buried inside longer strings like 'Contact John at john@example.com for details.' You need a clean column of addresses you can paste into Mailchimp, upload to a sales tool, or just filter. That means extracting reliably, not just eyeballing.

This page shows you working formulas and AI-generated approaches for extracting email addresses from any text in Google Sheets. The formulas here handle the standard cases: single email per cell, emails embedded in sentences, and lists with mixed formatting. Pick the approach that matches your data and you can have a clean output column in under five minutes.

When to use this

This approach works best when your data is already inside Google Sheets and you need a repeatable, formula-driven extraction that updates automatically as new rows come in. It is the right tool when emails are embedded in free-form text, imported from a form response, or pasted from an export where the address is surrounded by other content.

• Cleaning a Google Form export where respondents typed their email inside a longer answer field
• Processing a CRM or database export where contact info is in a single combined column like 'Name | Email | Phone'
• Pulling emails from scraped web content or copied directory listings pasted into a sheet
• Standardizing a marketing list where some rows have plain emails and others have names followed by addresses
• Building a live pipeline where new rows are added regularly and the extraction formula should apply automatically

When this format breaks down

• If a single cell contains multiple email addresses, REGEXEXTRACT returns only the first match by default. You need REGEXEXTRACT with an array workaround or a script, which quickly becomes fragile.
• If your emails use non-ASCII characters or internationalized domain names (like .香港 or accented local parts), standard regex patterns will miss them or produce wrong results.
• If you are working with thousands of rows and need bulk processing with deduplication, validation, and export, a dedicated data cleaning tool or a simple Python script will be faster and more reliable than nested sheet formulas.
• If the source text has no consistent structure and emails appear in wildly varying formats across cells, regex accuracy drops and you will need manual review anyway, making the formula less useful than a direct AI prompt on the raw data.

What makes these work

1. 01
   Always wrap with IFERROR

   REGEXEXTRACT throws a #N/A error when it finds no match, which breaks downstream formulas like COUNTIF or array operations. Wrap every extraction formula in IFERROR(formula, "") to return a blank instead. This keeps your output column clean and safe to use as a source range.

2. 02
   Test your regex on edge cases first

   Before dragging a formula down 500 rows, test it on five cells that represent your messiest data: one with no email, one with a subdomain address, one with a plus-sign alias like user+tag@domain.com, and one where the email is at the very start or end of the string. Regex patterns that look correct often fail on edge cases you did not anticipate.

3. 03
   Use a helper column, not in-place replacement

   Extract to a new column rather than overwriting the source. This preserves the original data for auditing and lets you re-run the formula if your pattern needs adjustment. Once you confirm the output column is correct, you can copy it and paste-as-values if you need a static result.

4. 04
   Validate results with a quick format check

   After extraction, add a second helper column with =ISNUMBER(MATCH("*@*.*",B2,0)) or a simple LEN check to flag any extracted value that looks wrong, like a cell that returned partial text instead of a full address. Catching bad extractions before you export saves cleanup time later.

More example scenarios

A spreadsheet column contains entries like: 'Sarah Mitchell, sarah.mitchell@brightcorp.io, Account Manager' and 'Follow up with dev team: raj.patel@techsolutions.com RE: Q3 proposal'. The user needs just the email addresses in a separate column for an email campaign upload.

Apply =REGEXEXTRACT(A2, "[a-zA-Z0-9._%+\-]+@[a-zA-Z0-9.\-]+\.[a-zA-Z]{2,}") in column B. For the two example rows this returns sarah.mitchell@brightcorp.io and raj.patel@techsolutions.com. Drag the formula down the entire column. Any cell with no email returns a #N/A error, which you can suppress with IFERROR.

A volunteer sign-up form asked 'How should we contact you?' and respondents typed things like 'You can reach me at volunteers@greencity.org anytime' or 'Email is best: j.torres2024@gmail.com, I check it daily.' The sheet has 340 rows and needs a clean email column for follow-up.

Use =IFERROR(REGEXEXTRACT(A2,"[a-zA-Z0-9._%+\-]+@[a-zA-Z0-9.\-]+\.[a-zA-Z]{2,}"),"") in column B. This handles the varied sentence structures and returns a blank instead of an error for any rows with no address. The result column is safe to copy and paste directly into an email platform.

A recruiter exported job listings from a board into Sheets. Column D contains the full job description text, which sometimes includes a contact line like 'Send resumes to hiring@novatech.com' or 'Questions? Reach out to careers@medbridge.org.' They want a column of just the contact emails to log outreach.

=IFERROR(REGEXEXTRACT(D2,"[a-zA-Z0-9._%+\-]+@[a-zA-Z0-9.\-]+\.[a-zA-Z]{2,}"),"no email found") extracts the first email in each description. Listings with no email get the fallback label so the recruiter knows to find contact info another way.

Two sales teams merged their contact lists into one sheet. One team stored data as 'firstname lastname <email@domain.com>' in Outlook format. The other stored plain emails. The merged column has both formats mixed across 600 rows and needs to output only the email address.

The same REGEXEXTRACT formula handles both formats because it matches the email pattern regardless of surrounding characters. For 'Jane Doe <jane.doe@acmecorp.com>' it correctly returns jane.doe@acmecorp.com. For a plain 'jane.doe@acmecorp.com' cell it returns the same result. One formula covers the entire mixed column.

A nonprofit coordinator pasted an alumni directory into Sheets. Each row is unstructured text like 'Michael Chen, Class of 2018, B.Sc. Engineering, mchen@alumni.stateu.edu, Chicago IL' or 'Lisa Park (lpark.2017@stateu.edu) - Program Coordinator'. They need the emails for a donation drive mailing list.

=IFERROR(REGEXEXTRACT(A2,"[a-zA-Z0-9._%+\-]+@[a-zA-Z0-9.\-]+\.[a-zA-Z]{2,}"),"") extracts mchen@alumni.stateu.edu and lpark.2017@stateu.edu respectively. The formula ignores parentheses, commas, and other surrounding punctuation and isolates only the address.

Common mistakes to avoid

• Using too simple a regex pattern

  A pattern like =REGEXEXTRACT(A2,".+@.+") matches too broadly and can grab long strings that include surrounding words. Use a character-class pattern like [a-zA-Z0-9._%+\-]+@[a-zA-Z0-9.\-]+\.[a-zA-Z]{2,} to match only valid email structures. Sloppy patterns produce dirty output that breaks mail platforms on import.

• Forgetting that REGEXEXTRACT only returns the first match

  If a cell contains two emails, the standard formula silently drops the second one. You will not see an error, you will just lose data. If your source data can have multiple addresses per cell, you need to either split cells first or use a Google Apps Script that loops through all matches.

• Not accounting for uppercase in email domains

  Some exports store emails with mixed case like User@Domain.COM. The regex pattern with [a-zA-Z] handles this correctly, but if you add any post-processing like LOWER() or exact-match lookups, verify your formula returns the address in the casing you need. Email addresses are technically case-insensitive in the domain part but case-sensitive in some systems for the local part.

• Copying the formula result without pasting as values before export

  If you copy the extracted email column and paste it directly into another tool or a new sheet, some applications receive the formula reference instead of the value, or the formula breaks because the source sheet is not included. Always use Paste Special > Values Only when moving extracted results out of the working sheet.

• Ignoring blank rows in the source column

  If your source column has blank cells mixed in, IFERROR handles errors but a blank source cell may still return an unexpected result depending on your formula structure. Filter or check for blanks before running bulk operations, especially if you are using the output column as a mailing list where empty rows cause import errors.

Related queries