Keep #N/A
=REGEXEXTRACT(A2,"[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}")Leaves a visible error when no email exists.
PASTE-READY RECIPE
Extract an Email Address from Text in Google Sheets
Pull the first email address out of a sentence, note or imported text block.
QUICK ANSWER
The formula and where it fits.
Pull the first email address out of a sentence, note or imported text block. Use =IFERROR(REGEXEXTRACT(A2,"[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}"),""), then replace the sample ranges with the columns in your sheet.
REGEXEXTRACT returns the first part of the text that matches the email pattern.
- Put the source data under one header row.
- Paste the formula into an empty result cell.
- Change the ranges and criteria to match your sheet.
- Check the result against one row you can verify manually.
TRY ITEdit the table.
Edit the table.
Watch the result change.
| A | |
|---|---|
| 1 | Text |
| 2 | |
| 3 | |
| 4 |
FORMULA OUTPUTmaya@example.com · blank · accounts@north.co.ukRecalculates from the input cells above
VARIATIONS
Use the version that fits your sheet.
Lowercase result
=LOWER(IFERROR(REGEXEXTRACT(A2,"[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}"),""))Normalizes the extracted address.
Across a column
=ARRAYFORMULA(IFERROR(REGEXEXTRACT(A2:A,"[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}"),""))Fills every row.
COMMON ERRORS
When the result looks wrong.
No address returns #N/A
Wrap REGEXEXTRACT in IFERROR when blank output is preferred.
Several addresses are present
REGEXEXTRACT returns the first match; split the text first when every address is needed.
QUESTIONS
Answers before you paste.
No address returns #N/A
Wrap REGEXEXTRACT in IFERROR when blank output is preferred.
Several addresses are present
REGEXEXTRACT returns the first match; split the text first when every address is needed.
MAKE IT YOURS
Adjust this formula →Change the ranges, labels or criteria.
Send this recipe to the SheetSteps assistant and describe how your columns are arranged.