Why emails end up buried in text
Contact details rarely arrive in neat columns. A sales rep types “reach her at [email protected] or +91 98765 43210” into a Notes field, a web form puts the whole message into one cell, and a list copied from an email thread lands as one long line per person. Before you can send a campaign or call anyone back, the address and number need their own columns.
How to extract emails manually in Excel
Flash Fill
- In an empty column next to the text, type the first email address exactly as it appears.
- Press Ctrl+E, or choose Data › Flash Fill.
Excel guesses the pattern and fills the rest of the column. It works well when every cell has the same shape, such as “Name - email”. When the address appears at a different position in each note, Flash Fill often guesses wrong, and it gives no warning, so check every row.
A formula for one address per cell
This classic formula returns the word containing the @ sign:
=TRIM(RIGHT(SUBSTITUTE(LEFT(A2,FIND(" ",A2&" ",FIND("@",A2))-1)," ",REPT(" ",100)),100))
It cuts the text at the first space after the @, swaps each space for 100 spaces, takes the last 100 characters and trims them. It finds only the first address in the cell, returns #VALUE! when there is no @, and keeps punctuation that is stuck to the address, so “[email protected],” comes back with the comma. Wrap it in IFERROR and clean up the leftovers.
REGEXEXTRACT in Microsoft 365
Recent Microsoft 365 builds include regular expression functions. This returns the first address in a cell:
=REGEXEXTRACT(A2,"[\w.+-]+@[\w-]+\.[\w.-]+")
Add a third argument of 1, as in =REGEXEXTRACT(A2,"[\w.+-]+@[\w-]+\.[\w.-]+",1), to return every match, which spills across the neighbouring cells. If your version of Excel does not have REGEXEXTRACT, you get a #NAME? error. A full stop at the end of a sentence can still be captured as part of the address.
Text to Columns and a filter
Copy the column, then use Data › Text to Columns with Delimited and Space to split each note into words. Filter the new columns for cells that contain “@”. This is quick for a short list but scatters addresses across many columns.
Power Query offers the same idea with more control: split the column by space into rows, then filter for text containing “@”.
Phone numbers are much harder
Numbers can be written as 98220 11223, (020) 2612-3456 or +34 612 345 678. A formula that catches all of them also tends to catch dates, invoice numbers and amounts. This is where most manual attempts give up.
Where this tool helps
CRM and lead exports. Notes fields full of “call back on…” and “send quote to…” become usable Email and Phone columns.
Event registrations and contact forms. Free-text answers where people typed their details in their own way.
Pasted and copied lists. Signatures, email threads and directory pages pasted into a sheet one line per person.
Preparing a mailing list. Extract the addresses before importing into Mailchimp, Zoho or HubSpot, which all expect one address per row.
With the sample file, the summary reads: “Found 4 email addresses in 3 rows and 5 phone numbers in 4 rows in Notes. Added the columns “Email” and “Phone”.” The order date 2026-09-14 in one note is not mistaken for a phone number.
How the tool decides what to extract
- Emails are any standard address, including addresses with international letters. Repeats within the same row are dropped, ignoring capitals.
- Phone numbers need 10 to 15 digits, or at least 8 when they start with a + country code. Spaces, dashes, dots and brackets are allowed inside the number.
- Dates, amounts and codes are ignored, so 15/03/2024, 1250.50 and INV9876543210 are not mistaken for numbers.
- Numbers are kept as written. Nothing is reformatted or given a country code it did not have.
- Source cells are untouched. Results go into new columns at the end of the sheet.
- CSV in, CSV out. The output keeps the format you uploaded. In a CSV, a number that starts with + is saved with a leading apostrophe, so Excel shows it as text instead of trying to calculate it.
Your contacts stay on your device
A list of names, emails and phone numbers is personal data. Uploading it to an unknown website to extract a column is a risk that privacy rules such as the GDPR and India’s DPDP Act ask you to think about. SheetTidy reads and processes the file inside your browser, so the contact details are never sent anywhere.
After extracting
Run Remove duplicates on the new Email column, because the same person often appears in several notes. Then remove extra spaces from the rest of the sheet before you import it.