If you’ve ever received a long list of messages, form responses, or website content pasted into a Google Sheet, you’ve probably seen email addresses and URLs buried in chunks of text. Manually copying them out can be time-consuming and error-prone. That’s where formulas in Google Sheets can save the day! With a few simple tricks, you can extract email addresses or website links from text in just seconds—perfect for anyone managing leads, contact info, or online content. Let’s dive in and make this easy.
Why Extract Emails or URLs Automatically?
When you’re dealing with unstructured data, like form responses or bulk-copied website content, useful info like emails or web links may be hidden inside paragraphs of text. Instead of reading and copying each one manually, you can use formulas to extract that data automatically. It’s faster, more accurate, and saves hours of repetitive work.
Real-Life Scenario: Pulling Contact Info from Form Responses
Imagine you collected feedback from a Google Form, and the open-ended responses look like this:
| Feedback |
|---|
| I loved the event! Check out my site at https://janeportfolio.com and feel free to email me at jane.doe@gmail.com |
| Reach out if you’d like to collaborate: my blog is www.blogideas.net — email: contact@blogideas.net |
| You can email me anytime: sam@creativeworld.org or connect via LinkedIn! |
Let’s extract the email addresses and URLs automatically.
Step-by-Step: Extracting Email Addresses
- Assume the feedback is in column A starting from A2.
- In a new column, use this formula to extract the first email:
=REGEXEXTRACT(A2, "[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}") - Drag the formula down for all rows.
Step-by-Step: Extracting URLs
To pull out URLs (including ones starting with http or www), use this formula:
=REGEXEXTRACT(A2, "(https?://[^\s]+|www\.[^\s]+)")This grabs the first occurrence of a URL from the text string.
Example Output
| Feedback | URL | |
|---|---|---|
| I loved the event! Check out my site at https://janeportfolio.com and feel free to email me at jane.doe@gmail.com | jane.doe@gmail.com | https://janeportfolio.com |
| Reach out if you’d like to collaborate: my blog is www.blogideas.net — email: contact@blogideas.net | contact@blogideas.net | www.blogideas.net |
| You can email me anytime: sam@creativeworld.org or connect via LinkedIn! | sam@creativeworld.org |
Pro Tips
- If there are multiple emails/URLs in one cell, REGEXEXTRACT will only return the first one. Consider using Google Apps Script or splitting into helper columns if needed.
- You can combine TRIM and IFERROR to clean up blank outputs:
=IFERROR(TRIM(REGEXEXTRACT(A2, "[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}")), "") - For full data cleanup, create columns for both emails and links side-by-side.
Key Benefits
- Quickly gather contact info from long text responses.
- Boost productivity and reduce manual errors.
- Ideal for managing outreach lists, support tickets, or feedback responses.
Quick-Reference Cheat Sheet
| Task | Formula |
|---|---|
| Extract email from text | =REGEXEXTRACT(A2, "[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}") |
| Extract first URL (http or www) | =REGEXEXTRACT(A2, "(https?://[^\s]+|www\.[^\s]+)") |
| Handle empty result safely | =IFERROR(REGEXEXTRACT(A2, "regex_here"), "") |
Extracting emails and URLs in Google Sheets doesn’t require coding skills or fancy add-ons. With the right formula and a little bit of regex magic, you can clean up your data and get exactly what you need. Whether you’re managing outreach lists, cleaning form submissions, or analyzing web content, this technique will save you hours of work and keep your data tidy and useful.