Excel can extract, split, build and match email addresses. It cannot find one that isn’t already sitting somewhere in your files. In 2021 a partner sent me a 1,400-row export where every useful detail (job titles, phone numbers, email addresses) was mashed into one free-text “Notes” column. I spent an afternoon writing a formula to pull the addresses out of it. Below are the five formulas that came out of that afternoon, plus the honest workaround for the rows no formula could ever save.
I’m going to be honest with you. My formula worked beautifully on row 2 and then broke on row 3. I’ll tell you exactly why in a minute.
Let’s get into it.
📌 TL;DR: Excel does four jobs with email addresses: EXTRACT them out of messy text, SPLIT the domain off, BUILD them from names, and JOIN them against another file with VLOOKUP or XLOOKUP. It does not do the fifth job: finding an address that isn't already in your workbook. For that one, you round-trip: open the .xlsx in Google Sheets, run an enrichment add-on on the column, then download it back as Excel.
What Can Excel Actually Do With Email Addresses?
Excel can extract, split, build and match email addresses using formulas you already own. Four jobs. And it’s worth naming them separately, because a search for “email lookup excel” throws all four back at you in one tangled pile of forum threads.
- Extract: pull an address out of a cell that also contains a name, a note, and half a phone number.
- Split: separate the domain from the local part, so you can group a list by company.
- Build: assemble a likely address from a first name, a last name, and a domain.
- Join: match an address against a second file and copy across whatever that file knows.
Every one of those is a text operation on Contact Data you already hold. That’s the quiet condition on all four. A worksheet is a very good pair of scissors and a very bad telephone: it rearranges what’s in front of it, and it never calls anyone to ask.
So let’s start with the job that sends most people to Google in the first place.
How Do You Extract Email Addresses From Excel?
Use a formula that pads every space, finds the @, and grabs the whole word around it. That’s the technique behind every reliable answer to this, and it works in Excel 2010 as happily as in the 2026 release. Four routes below, from the one that always works to the one that’s fastest when your data behaves.
The classic formula (works in every version)
Say the messy text is in A2. Drop this into B2 and fill it down:
=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",100)),MAX(1,FIND("@",SUBSTITUTE(A2," ",REPT(" ",100)))-50),100))
Now here’s what it’s actually doing, because pasting a formula you don’t understand is how you end up with 400 broken cells and no idea which one lied to you.
- SUBSTITUTE swaps every single space for 100 spaces. Each word in the cell now floats in its own ocean of whitespace.
- FIND locates the @ inside that padded version, not the original.
- MID grabs a 100-character window starting 50 characters before the @. MAX(1,…) stops the start position dropping below 1 when the address sits right at the front of the cell.
- TRIM sweeps the padding away and leaves exactly one thing standing: the word that contained the @.
Test it on Called Lena Fischer, lena.fischer@hansellogistics.com, ops lead and you get lena.fischer@hansellogistics.com back, with a comma stuck to the end of it.
And there it is. That comma is what broke my afternoon in 2021. The formula returns the whole space-delimited word, so any punctuation touching the address rides along: commas, semicolons, closing brackets, angle brackets. Row 2 of my file had a space after the address. Row 3 had a comma. Same formula, two different answers.
The fix is a nested SUBSTITUTE pass over the result. Point it at B2, the column you just built:
=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,",",""),";",""),"(",""),")",""),"<",""),">",""))
Notice what I did NOT strip: the period. Kill every dot and you kill the domain too. But a sentence-ending period is a real problem: “write to lena@hansellogistics.com.” leaves you a trailing dot. So handle that one on its own:
=IF(RIGHT(B2,1)=".",LEFT(B2,LEN(B2)-1),B2)
Two more honest limits before you fill 5,000 rows. First, the window is 100 characters wide, so a local part longer than 50 characters gets clipped (rare, but check anything that looks truncated). Second, a row with no @ anywhere returns a #VALUE! error. That’s genuinely useful. It’s a free filter for the rows that have nothing to extract, and in my Notes column exactly 300 of the 1,400 lit up that way.
The Microsoft 365 shortcut
If you’re on Microsoft 365, two newer text functions do the same job in a shape that’s easier to read a year later:
=TEXTAFTER(TEXTBEFORE(" "&A2&" ","@")," ",-1)&"@"&TEXTBEFORE(TEXTAFTER(" "&A2&" ","@")," ")
It reads left to right: take everything before the @, keep the text after the LAST space in it, glue the @ back on, then take everything after the @ up to the first space. The extra ” “& and &” ” pad the cell at both ends so the formula still works when the address starts or finishes the text.
One caveat worth flagging loudly. TEXTAFTER and TEXTBEFORE are recent additions, so they don’t exist in Excel 2019 or 2016. Share a workbook that uses them with a colleague on an older build and they’ll see #NAME? errors everywhere. When the file travels, use the classic version above.
When Flash Fill is faster than any formula
Sometimes you don’t need an excel formula to find email address values at all. Type the correct answer for row 2 by hand, type it for row 3, then press Ctrl+E. Flash Fill spots the pattern and completes the rest of the column.
It’s genuinely magic on consistent data. But it guesses, and it guesses silently, so spot-check twenty random rows before you trust it. And the results are static values, not a live formula, which means they don’t update when the source column changes. That’s fine for a one-off clean-up and wrong for a file you’ll re-import monthly.
Text to Columns for one-per-cell lists
When your cell holds “Lena Fischer <lena.fischer@hansellogistics.com>” or a comma-separated pair, skip the formulas entirely. Select the column, then Data > Text to Columns > Delimited, and pick the character that separates them.
The rule of thumb I use: one clean, consistent delimiter means Text to Columns. Anything messier means the padding formula. My Notes column was firmly in the second camp.
💡 Pro Tip: Once your extracted column looks right, copy it and paste it back over itself as VALUES before you touch the source cells. Delete column A while B still references it and you'll watch a thousand addresses turn into #REF! in one keystroke. Ask me how I know.
How Do You Split the Domain Off an Email Address?
Take everything to the right of the @ with RIGHT and FIND, or with TEXTAFTER on Microsoft 365. Both versions, assuming a clean address in A2:
=LOWER(TRIM(RIGHT(A2,LEN(A2)-FIND("@",A2))))
=LOWER(TEXTAFTER(A2,"@"))
The classic one measures the whole string with LEN, subtracts the position of the @, and asks RIGHT for that many characters. LOWER matters more than it looks: Sales@Acme.com and sales@acme.com should never count as two companies. And if you want the name side instead of the domain side, flip it:
=LOWER(LEFT(A2,FIND("@",A2)-1))
=LOWER(TEXTBEFORE(A2,"@"))
Now, why bother? Because a domain column is a grouping key, and that’s the part the forum answers skip. Once every row carries its domain, you can count contacts per account with COUNTIF, spot the free consumer domains hiding in a B2B list, and sort a flat list of 800 people into the 120 companies they actually work for. It’s the cheapest piece of Data Extraction in the whole workbook.
A domain also travels further than a person’s name does. Feed it back into a service and you can turn a domain into a company name, which is usually the next column people want.
How Do You Do a VLOOKUP for Email Addresses in Excel?
Match on the address itself and always pass FALSE, so Excel demands an exact match. Here’s the pair, looking up A2 against a sheet called Contacts:
=VLOOKUP(A2,Contacts!$A:$B,2,FALSE) =XLOOKUP(A2,Contacts!$A:$A,Contacts!$B:$B,"")
XLOOKUP’s fourth argument is the reason I’ve mostly switched. Pass “” and unmatched rows come back blank instead of littering your sheet with #N/A.
Here’s the part that matters more than the syntax, though. A lookup is a JOIN, not a search: it copies a value from a table you already have. So #N/A means “this address isn’t in MY file.” It never means “this person doesn’t have an email.” My colleague at a former agency once wrote off 600 rows as unfindable on exactly that misreading. (The full argument about what formulas can and can’t fetch lives in the data enrichment in Excel guide, so I won’t repeat it here.)
When a join fails on email addresses, it’s almost always one of three things:
- Approximate match left on. Forget the FALSE and VLOOKUP returns the nearest thing it finds. On email addresses that’s nonsense dressed as data.
- Trailing spaces from a CSV export. “lena@acme.com ” and “lena@acme.com” are two different strings to Excel, and you cannot see the difference on screen.
- Case drift. Excel’s lookups ignore case, so this one hides, until you export to a system that doesn’t ignore it, or paste the column into a tool that treats Lena@ and lena@ as separate records.
One cleaned helper column solves all three. Put this in a spare column in BOTH files, then look up against the helper instead of the raw column:
=LOWER(TRIM(A2)) =XLOOKUP(LOWER(TRIM(A2)),Contacts!$D:$D,Contacts!$B:$B,"")
How Do You Build Email Addresses From Names in Excel?
Join the name parts and the domain with &, then wrap the whole thing in LOWER. With the first name in A2, the last name in B2 and the domain in C2:
=LOWER(TRIM(A2)&"."&TRIM(B2)&"@"&TRIM(C2)) =LOWER(LEFT(TRIM(A2),1)&TRIM(B2)&"@"&TRIM(C2))
The first gives you lena.fischer@hansellogistics.com. The second gives lfischer@hansellogistics.com. Those two patterns cover a big share of B2B mailboxes, and the TRIM calls matter because pasted name columns are riddled with invisible spaces.
Then check the shape of what you built. If the results are in D2:
=IF(COUNTIF(D2,"*?@?*.?*"),"shape ok","check this")
COUNTIF reads the wildcards like a sentence: at least one character, an @, at least one character, a dot, at least one more character. It catches the genuinely broken rows: the missing surname, the blank domain, the stray line break.
Now the warning, and I’d like you to read this one twice. A shape check is NOT verification. “shape ok” means the string looks like an email address. It says nothing about whether a mailbox exists behind it. Real Data Verification checks syntax, then whether the domain accepts mail, then whether the specific mailbox responds. Excel can only ever do the first of those three.
So every address in that column is a guess until something outside Excel says otherwise. Send to guesses and the wrong ones come back as a Hard Bounce, and mailbox providers keep score. At minimum, run the domain through the Google Admin Toolbox MX check to confirm it receives mail at all. For the full set of patterns (and there are more than two), I’m writing a dedicated email permutator guide for this folder.
Is There an Email Finder for Excel?
No native Excel feature returns a verified work email. There’s no hidden function, no built-in email finder excel users have been missing, and no formula that phones a database. This is the honest answer that the product pages ranking for this query won’t give you.
Third-party add-ins do exist. Microsoft’s Office Add-ins platform is what vendors build them on, and a few email tools ship one. Quality varies wildly, and an add-in that stops being maintained becomes a broken pane in your ribbon.
Which leaves two routes that reliably work. Route one: export the sheet as a CSV and upload it to an enrichment platform, then download the filled file and open it back in Excel. No Google account required, and it suits a one-off file you just want returned fuller.
Route two: round-trip through Google Sheets and run an add-on there. I’ll be straight with you about why: CUFinder’s add-on is Sheets-native, and there is no CUFinder Excel add-in. So Excel users take a short detour, and the same workflow is already documented as an email finder in Google Sheets. Route two wins when the list is one you’ll keep working in. If your starting point is a column of company domains rather than people, that’s a bulk email finder job instead.
How Do You Run the Lookup Outside Excel and Bring It Back?
Open the .xlsx in Google Sheets, run the add-on on your columns, then download it as Excel again. It sounds like a detour. In practice it’s about ten extra clicks, and here’s every one of them.
Step 0: get the file into Sheets. Drag the .xlsx into Google Drive and open it with Sheets, or use File > Import from inside a blank sheet. Sheets opens and edits Excel files directly, so your columns arrive intact.
- Install the add-on. Grab the CUFinder add-on from the Google Workspace Marketplace. Google’s add-ons help page explains where installed add-ons show up.
- Copy your API key. Head to your CUFinder dashboard and copy the API key from there.
- Enter the key in the add-on. Paste it once and you’re connected.
- Pick the right service. Open the add-on from the Google Sheets menu. It opens as a right panel listing all the enrichment services. Your input column decides: names plus companies → Contact Enrichment, which will find a professional email by name and company. A column of LinkedIn profile URLs → the service that returns the work email from a LinkedIn URL.
- Map columns, set the range, run. Set the input column, the output column where emails should land, and the row range: rows 2-6 for a test, rows 2-1401 for my old Notes file. Then run it.
When it finishes: File > Download > Microsoft Excel (.xlsx), and your workbook is back on your desktop with a filled column.
Two honest notes. Unmatched rows come back empty, because no provider matches everybody, and an empty cell is the tool telling you the truth rather than handing you a guess. Also check your number and date formatting after the round-trip; conversions are good, not sacred.
And test rows 2-6 first. Always. Five rows will tell you whether your columns are mapped correctly, and five rows cost almost nothing to get wrong.
A Worked Example: 5 Rows, Before and After
Say your workbook starts like this, with names in column A, companies in column B, and a work email column sitting empty:
| A | B | C | |
|---|---|---|---|
| 1 | Name | Company | Work email |
| 2 | Lena Fischer | Hansel Logistics | |
| 3 | Marco Ruiz | Bluepine Software | |
| 4 | Priya Nair | Corvid Analytics | |
| 5 | Tom Okafor | Marlow & Sons | |
| 6 | Sofia Berg | Tidewater Robotics |
I dragged that .xlsx into Drive, opened it in Sheets, and ran Contact Enrichment: input columns A and B, output column C, rows 2 to 6. About a minute later the same file looked like this.
| A | B | C | |
|---|---|---|---|
| 1 | Name | Company | Work email |
| 2 | Lena Fischer | Hansel Logistics | lena.fischer@hansellogistics.com |
| 3 | Marco Ruiz | Bluepine Software | marco.ruiz@bluepinesoftware.com |
| 4 | Priya Nair | Corvid Analytics | priya.nair@corvidanalytics.com |
| 5 | Tom Okafor | Marlow & Sons | |
| 6 | Sofia Berg | Tidewater Robotics | sofia.berg@tidewaterrobotics.com |
Look at row 5. Tom’s cell is still empty, and that’s the result I trust most on this whole page: no match, no guess, no junk address quietly poisoning a send. Then File > Download > Microsoft Excel, and the workbook came home with column C mostly full. Happy with the test? Change the range to your real row count and run it once more.
→ .xlsx → Sheets → one run → download → back in Excel with a filled column.
📌 Example: The 2021 Notes column, 1,400 rows. The padding formula plus the punctuation clean-up rescued about 1,100 addresses in one afternoon. The other 300 rows had no address in the text at all, and no excel formula was ever going to conjure them. Those went out and came back.
What Mistakes Should You Avoid With Email Data in Excel?
Overwriting your source column, trusting a shape check as verification, and leaving duplicates in the file. Those are the three that cost me the most time. Here’s the full scar map:
- Writing formula results over the source column. Extract into a NEW column, always. Your messy original is the only thing you can re-run when the formula turns out to be wrong on rows 400 to 900.
- Treating “shape ok” as verified. The wildcard check proves a string looks like an email. It proves nothing about the mailbox. Two different claims, and only one of them protects your sender reputation.
- Leaving duplicates in. Flag them with =COUNTIF($A$2:$A$500,A2)>1, then use Data > Remove Duplicates once you’ve seen what you’re deleting. Proper Deduplication starts with the lowercased, trimmed version of the column, or you’ll miss half of them.
- Mixing guessed and verified addresses in one column. Six weeks later nobody remembers which is which. Keep built addresses in their own column, clearly labelled, until something verifies them.
- Forgetting that CSV export flattens formulas. Save as CSV and every formula becomes its last calculated value. That’s usually what you want. Just know it happened, and keep the .xlsx as your working copy.
- Never re-checking an aging column. Data Decay is relentless: people change jobs and their addresses die with the old badge. Add a “checked on” column next to your emails and re-run anything older than six months.
🔍 Did You Know? Google's bulk sender guidelines cap the spam-complaint rate at 0.3% (three complaints per thousand emails). A column of unverified guesses will take you past that ceiling faster than you'd believe.
So that’s the mistakes list. Now the questions people ask me most about this exact job.
FAQ: Email Lookup in Excel
How do you separate email addresses from names in Excel?
If the name and address share a cell with a clean separator, use Data > Text to Columns and split on that character. If the cell is messy free text, use the padding formula instead: it finds the @ and returns only the word wrapped around it.
How do I get Excel to autofill email addresses?
Type the correct result for the first two rows, then press Ctrl+E to trigger Flash Fill. Excel reads your pattern and fills the rest of the column. The catch: those are static values, so they never refresh when the source data changes. Spot-check before you rely on them.
How do I get a list of emails into Excel?
Three normal routes: paste them straight in, import a CSV through Data > From Text/CSV, or export from your CRM. Whichever you pick, format the email column as Text first. Excel occasionally decides an address is something else, and you’ll spend an hour undoing that helpfulness.
How do I extract email addresses from a CSV file?
Open the CSV in Excel and use exactly the same formula you’d use on any worksheet. The file type changes nothing about the technique: a CSV is just text with commas. Import it properly rather than double-clicking, so Excel doesn’t reformat your columns on the way in.
Can I copy and paste email addresses from Excel to Outlook?
Yes. Join the column into one string with =TEXTJOIN(“; “,TRUE,C2:C200), then paste that into the recipient field. On older Excel, build the same string in a helper column with =D1&C2&”; ” filled down. Mind your recipient limits, and use BCC so nobody sees the list.
How do I find duplicate or invalid email addresses in Excel?
COUNTIF flags duplicates across a range, and the wildcard shape check catches the obviously broken ones. Missing @, missing dot, nothing after the domain. Neither proves a mailbox exists, though. Both are hygiene, not verification, and confusing the two is how bounce rates climb.
What is the best email lookup tool?
The one that performs best on YOUR list, which is rarely the one with the longest feature page. Take 100 real rows, run them through two or three candidates, and compare fill rate and accuracy side by side. Where your data already lives matters more than any feature comparison.
Does Excel have an email finder add-in?
Some vendors ship one, but Excel has no native feature that returns a verified address. So most people round-trip: export a CSV to an enrichment platform, or open the file in Google Sheets and run an add-on there. Ten extra clicks, and the column comes back filled.
It’s Time to Stop Fighting That Notes Column
You’ve got the whole toolkit now. The padding formula for messy text. RIGHT and FIND for the domain. VLOOKUP or XLOOKUP for the join. LOWER and & for building addresses, plus the wildcard check to catch the broken ones. And the round-trip for the rows Excel simply cannot answer.
Picture next Monday. That export lands in your inbox, you paste one formula down 1,400 rows, and the column that used to eat an afternoon is done before your coffee cools. The rows that come back empty go out and come home filled. No nested guessing, no wishful thinking.
Email is only the first column, of course. The rest of the data enrichment in Google Sheets hub covers everything else you might want beside it. Tell me in the comments: what’s the worst free-text column anyone has ever sent you?
Bad
Okay
Good
Amazing
