Email addresses
How to Fix Email Typos in Excel: gmial.com, .con and Missing @
Trim the spaces and lowercase the whole column first, then fix known provider typos with SUBSTITUTE (for example =SUBSTITUTE(A2,"@gmial.com","@gmail.com")), flag every address that still lacks exactly one @ sign or a dot after it, and look at those by hand. Never "fix" an address you are not sure about: a wrong guess sends mail to a stranger.
A mistyped email address does not announce itself. It sits in the column until the day you send a campaign, an invoice or a login link and it bounces, or worse, reaches someone else. This guide shows how to find the usual mistakes in an Excel or CSV file and fix the safe ones, in the order that causes the least damage.
The typos you will actually see
| What you find | What it should be | Why it happens |
|---|---|---|
john@gmial.com, john@gmaul.com, john@gnail.com | john@gmail.com | Neighbouring keys on the keyboard |
mary@outlok.com, mary@hotmial.com | mary@outlook.com, mary@hotmail.com | Dropped or swapped letters |
anna@yahoo.con, anna@yahoo.cmo | anna@yahoo.com | The ending typed too fast |
bob@@example.com | bob@example.com | A doubled key press |
peter.smithgmail.com | peter.smith@gmail.com | The @ was never typed |
sam@yahoo,com | sam@yahoo.com | A comma where the dot belongs |
Lena@Example.com | lena@example.com | Stray spaces and capital letters |
Step 1: remove spaces and make everything lowercase
Email domains are not case sensitive, and spaces are never valid inside an address. In a helper column next to your emails, enter:
=LOWER(TRIM(A2))Fill it down, then copy the column and use Paste Special, Values, over the original. If your data came from the web, a non-breaking space (character 160) may be hiding in there too; see our guide on removing extra spaces.
Step 2: fix the provider typos you are sure about
For a short list of well-known typos, SUBSTITUTE is safe because it only changes text that matches exactly:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"@gmial.com","@gmail.com"),"@gmaul.com","@gmail.com"),"@outlok.com","@outlook.com")You can also use Find and Replace (Ctrl+H), tick "Match entire cell contents" off, and replace @gmial.com with @gmail.com. Keep the @ in the text you search for, so you never touch a name that happens to contain those letters.
Tip: fix endings the same way: replace .con with .com only when it is at the very end. A formula such as =IF(RIGHT(B2,4)=".con",LEFT(B2,LEN(B2)-1)&"m",B2) does exactly that and nothing else.
Step 3: flag what is still wrong
After the safe fixes, mark every address that does not look like an address. This formula returns TRUE for something plausible:
=AND(LEN(B2)-LEN(SUBSTITUTE(B2,"@",""))=1, ISNUMBER(FIND(".",B2,FIND("@",B2))), LEFT(B2,1)<>"@", RIGHT(B2,1)<>".")It checks for exactly one @, a dot somewhere after it, and that the address does not start with @ or end with a dot. Filter the column for FALSE and review those rows yourself.
Warning: this tells you an address is shaped like an email. It cannot tell you the mailbox exists. Only sending (or an email verification service) proves that.
Step 4: find repeats
Typos create near-duplicates, and the same person often appears twice. After cleaning, count repeats with:
=COUNTIF($B$2:$B$5000,B2)Anything above 1 is a repeat. Our guide on removing duplicates covers what to keep.
Check a short list for free
If you only have a list to check, paste it into our free email typo checker. It runs in your browser, so nothing is uploaded, and it fixes only the mistakes that are almost certainly typos.
When a spreadsheet is too big for formulas
Formulas work well for a few hundred rows. With tens of thousands of rows and many kinds of mistakes at once (emails, phone numbers, dates, names), it is slow and easy to get wrong. DecodingPro fixes the safe cases, re-opens the finished file and compares it with your original, and flags the unclear cells instead of guessing.
You start with 100 free credits. Every change is logged, and anything unclear is flagged, never guessed.
Fix a spreadsheetQuestions people ask
Can Excel check that an email address really exists?
No. Excel can only check the shape of the address. To know that a mailbox exists you need to send to it or use an email verification service.
Is it safe to replace gmial.com with gmail.com for the whole column?
Yes, when you search for the full text @gmial.com. It is a very common keyboard slip and almost never a real domain. Still, spot-check a few rows before saving.
Why did my fixed emails come back as hyperlinks?
Excel turns email-like text into links when you type or paste it. Right-click one, choose Remove Hyperlink, or select the column and use Clear, Clear Hyperlinks.