✉️ Data Extraction · 7 min read

How to Extract Emails From a CSV or Spreadsheet (2026)

To extract email addresses from a CSV, run a pattern match over the text and collect every match. In Google Sheets, =REGEXEXTRACT(A2,"([^\s@]+@[^\s@]+\.[^\s@]+)") pulls the first address out of a cell. Excel 365 has the same function, REGEXEXTRACT, added in 2024; older Excel needs Power Query or a MID and SEARCH formula. In Python, re.findall finds every address in one pass, and pandas str.findall does it column by column. Whatever you use, lowercase the results and dedupe them before the list goes anywhere near a mail tool.

Where Email Addresses Hide in a CSV

The tidy case is a column already labelled email. That is rare. Most of the time the addresses are scattered through text that was pasted from somewhere else:

• A notes or comments field, holding a phone number, an address, and one email in the middle of a sentence.
• A contact field with two or three people in it, separated by a comma or a slash.
• A support-ticket body, where the sender is on the first line and someone else is quoted further down.
• A column of raw text exported from a form, with no field separation at all.

Because a CSV field can legally hold commas and line breaks inside quotes (that is RFC 4180), a cell can contain a whole paragraph. Matching a pattern against each cell is the right approach; splitting columns is not.

Method 1: Google Sheets

Sheets has had a regex function for years, which makes it the quickest place to test a pattern.

1. Put the source text in column A.
2. In B2, enter =REGEXEXTRACT(A2,"([^\s@]+@[^\s@]+\.[^\s@]+)").
3. Fill the formula down the column.

One limitation to plan around: REGEXEXTRACT returns a single match. If a cell holds two addresses, you get the first and the second is quietly dropped. For a list where most cells are one-per-cell, that is fine. Where it is not, split the cell first with SPLIT, or move to Python.

To split before matching, =SPLIT(A2,",") spreads the values across columns and you run the extract on each new column. It is crude, but it is easy to check by eye, and that matters when the list is going to be emailed.

Method 2: Excel

Excel has two eras to think about.

Excel 365 (2024 and later) shipped REGEXEXTRACT, matching Sheets. The syntax is the same, and the same one-match limit applies.

Excel 2016 to 2021 has no regex function at all. The two workable routes are Power Query and a careful formula. Power Query is the better of the two: load the table, split the column on the separator, unpivot so every fragment lands in one column, then add a custom column with a Text. pattern, or filter down to the rows that contain an at sign. The advantage is that Power Query refreshes, so next month's export is one click instead of the whole job again.

The formula route exists and it is unpleasant. A common pairing is MID with SEARCH(""@"",A2) to find the at sign, then SEARCH backwards for the space or comma that starts the address. It works on clean input, and it breaks on the first cell that does not look like the last one. If you find yourself nesting a fourth function, that is the signal to switch to Power Query or Python.

Method 3: Python, the Version That Handles Mess

One pass over the file pulls everything out, and the code is short enough to keep.

import csv
import re

pattern = re.compile(r"[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}")

found = []
with open("contacts.csv", newline="", encoding="utf-8") as f:
    for row in csv.reader(f):
        for cell in row:
            found.extend(pattern.findall(cell))

# Domain names are case-insensitive, so normalise before deduping
unique = []
for address in found:
    key = address.lower()
    if key not in unique:
        unique.append(key)

print(len(found), "matches,", len(unique), "unique")
print(unique[:5])

Two details in that pattern earn their keep. The character class starts with A-Za-z so a fragment like @word with nothing before it is ignored. The dot before the final letters is escaped as \\. so it has to be a real dot, which stops a string like name@localhost from counting as an address.

If the data is already a DataFrame, pandas does the same thing per column:

import pandas as pd

df = pd.read_csv("contacts.csv", dtype=str)
emails = (
    df["notes"]
    .str.findall(r"[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}")
    .explode()
    .dropna()
    .str.lower()
    .unique()
)
print(len(emails))

The explode call is the part people miss. str.findall returns a list per row, so without explode you get a column of lists rather than a flat set of addresses.

Cleaning the List Before You Use It

Extraction is the easy half. A raw match set usually needs four passes before it is usable.

1. Trim the whitespace. A trailing space or newline clings to the end of a match and makes a perfectly good address bounce.
2. Lowercase it. The domain is case-insensitive, and in practice every large provider treats the whole address that way. Normalising catches the duplicates that a case-sensitive pass would miss.
3. Dedupe. Then compare the count before and after. A list that halves is a sign the source had the same person in several rows. The duplicate removal guide covers the spreadsheet side.
4. Check the shape. Anything without an at sign, anything with two, and anything with a space inside it is worth a look. A pattern match is not a validation.

If the source file was messy to begin with, clean it before extracting, not after. Fixing stray quotes, wrong delimiters and blank rows is simpler while the data is still tabular. The dirty data guide is the checklist for that stage, and if the addresses live in a PDF rather than a CSV, the PDF to CSV extraction guide covers the conversion step.

The Rules That Apply Once You Have the List

Collecting addresses is a data-cleaning task. Emailing them is a regulated one, and the rule that applies depends on where the recipient is.

In the United States, CAN-SPAM does not require consent for commercial email, but it requires accurate headers, a clear way to opt out, and prompt honouring of that opt-out. The FTC enforces it and can seek a civil penalty for each separate message that breaks the rules, with the adjusted maximum per email running above $50,000.

For recipients in the EU or the UK it is stricter. An email address is personal data, and GDPR requires a lawful basis before you process it. Consent is one; legitimate interest is another, and it has to be documented. Fines under Article 83 reach 20 million euros or 4 percent of total worldwide annual turnover, whichever is higher, so the list itself is the smallest part of the risk.

None of this bans the task. It does mean the person who scraped a list should hand it to the person who decides whether to email it, with the source written down.

Which Method Fits

Two Things to Check Before You Trust the Output

Count your rows. If a 5,000-row file returns 40 addresses, the pattern is too strict or the column is emptier than it looks, and both are worth knowing before you build anything on top of the result. The free CSV analyzer shows column fill rates, which answers that question in a glance rather than a formula.

Then spot-check ten matches against the source text by hand. A pattern that is slightly too loose picks up fragments that look right at a distance and fail on send. Ten manual checks is a few minutes and it is the difference between a list and a guess.

Frequently Asked Questions

How do I extract email addresses from a CSV?

Match a pattern against the text of each cell and collect what matches. Sheets and Excel 365 use REGEXEXTRACT for the first match in a cell; older Excel uses Power Query; Python's re.findall returns every match in one pass.

What regex should I use for email addresses?

A practical pattern is a run of letters, digits and the characters . _ % + - followed by an at sign, a domain, a dot, and two or more letters. It allows plus addressing and subdomains, which a plain word-character pattern misses.

Why does my formula only return one email per cell?

Because REGEXEXTRACT is built to return a single match, so it stops at the first one. Where a cell holds several addresses, split the cell first, add a helper column, or move to Python where re.findall returns the whole list.

How do I remove duplicate emails from the extracted list?

Lowercase first, then dedupe. The domain part of an address is case-insensitive, so a case-sensitive pass leaves both Jane@Example.com and jane@example.com. In a spreadsheet use Remove duplicates; in Python build a set from the lowercased values.

How long can an email address be?

RFC 5321 caps the local part at 64 octets and the domain at 255, which puts the practical ceiling for a full address at 254 characters. A regex that assumes something short will miss the long ones, and a 50-character database column will cut them off.

Is it legal to extract and email these addresses?

Extracting is a data task, emailing is a legal one. CAN-SPAM allows commercial email with accurate headers and a working opt-out, and the FTC can penalise each offending message. For EU or UK recipients, GDPR needs a lawful basis, with fines reaching 20 million euros or 4 percent of worldwide turnover.

Can I extract emails from a PDF or a scanned file?

Only if there is a text layer. A PDF exported from Word or Excel carries selectable text, so convert it to CSV or plain text first. A scanned page is an image and needs OCR before any pattern match can work.