Extract the Domain or Username From an Email in Excel

To extract the domain from an email address in Excel, you take everything after the @ sign; for the username, you take everything before it. In Microsoft 365, TEXTAFTER and TEXTBEFORE do this with one short formula each. In any version, MID, LEFT and FIND do the same job. This guide shows both ways, plus how to get the company name, count by domain, and handle messy addresses.

All the examples use this list of email addresses:

How email addresses split#

Every email address has exactly one @ sign. The part before it is the username, and the part after it is the domain. So all you need is the position of the @, and FIND gives you that:

=FIND("@",A2)

For "maria.lopez@contoso.com", the result is 12: "maria.lopez" is 11 characters, so the @ is the 12th. Everything else builds on this number.

Extract the domain with TEXTAFTER#

TEXTAFTER returns the text after a delimiter, which is a character that marks where to split:

=TEXTAFTER(A2,"@")

The result is contoso.com. Fill it down and you get fabrikam.com, example.org and tailspintoys.com. TEXTAFTER needs Microsoft 365, Excel 2024 or Excel for the web. The TEXTBEFORE and TEXTAFTER lesson has more examples.

Extract the domain with MID and FIND#

In older versions of Excel, use MID to take the text starting just after the @:

=MID(A2,FIND("@",A2)+1,LEN(A2))

Here is how it works for maria.lopez@contoso.com:

LEN(A2) asks for more characters than are left, and that's fine: MID just stops at the end of the text. It's a simple way to say "take the rest". You could also use =RIGHT(A2,LEN(A2)-FIND("@",A2)), which takes 23 - 12 = 11 characters from the right. The MID function guide and FIND function guide explain each part, and the MID function article has more worked examples.

Extract the username#

The username is everything before the @. In Microsoft 365:

=TEXTBEFORE(A2,"@")

In any version, take the characters from the left up to one before the @:

=LEFT(A2,FIND("@",A2)-1)

Both return maria.lopez. The -1 stops LEFT just before the @, so the @ isn't included. For the pros and cons of each approach, see LEFT vs TEXTBEFORE.

Many usernames follow a first.last pattern. To split "maria.lopez" into a first and last name, use the same trick with the dot: =TEXTBEFORE(TEXTBEFORE(A2,"@"),".") returns maria. The guide to splitting a full name shows the same method on names with spaces.

Get the company name from the domain#

Sometimes you want "contoso" rather than "contoso.com". That's the text between the @ and the first dot after it:

=TEXTBEFORE(TEXTAFTER(A2,"@"),".")

The result is contoso. For a domain like "mail.contoso.co.uk", this returns "mail", so check your data for subdomains first. Extracting text between two different characters is covered step by step in how to extract text between characters.

Extract the top-level domain#

The top-level domain is the last part of the address, such as com, org or uk. TEXTAFTER can count delimiters from the end when you give it a negative instance number:

=TEXTAFTER(A2,".",-1)

This returns the text after the last dot: com for maria.lopez@contoso.com and org for sam.okoro@example.org. The -1 means "the first dot counting from the right". It's a quick way to group contacts by country domains like .de or .fr.

Extract domains without a formula#

Two menu tools can split emails without any formula:

• Flash Fill. In B2, type the domain of the first email by hand, such as contoso.com. Start typing the second, and Excel suggests the rest. Press Enter to accept, or use Data > Flash Fill (Windows: Ctrl+E).
• Text to Columns. Select the emails, choose Data > Text to Columns, pick Delimited, and type @ in the Other box. Excel puts usernames in one column and domains in the next.

Both are fast for a one-off list, but the results don't update when the emails change. Formulas do, so use them when the list keeps growing.

Clean the addresses first#

Email lists collected from forms are rarely tidy. Before extracting, clean the column so the same domain doesn't appear in different forms:

=LOWER(TRIM(A2))

TRIM removes stray spaces, and LOWER turns "Contoso.com" and "CONTOSO.COM" into the same text. Email addresses aren't case-sensitive in practice, so lower case is the safe standard. Then run your domain formula on the cleaned column.

Count emails by domain#

Once you have a Domain column, a COUNTIF or a PivotTable tells you how many contacts come from each company. You can also skip the helper column and count straight from the addresses with a wildcard:

=COUNTIF(A2:A5,"*@contoso.com")

The * stands for any username, so this counts every address ending in @contoso.com: 1 in the example list. In Microsoft 365, =UNIQUE(TEXTAFTER(A2:A5,"@")) lists each domain once.

Fix errors for cells without an @#

If a cell has no @, because it's empty or holds a typo, FIND returns #VALUE! and TEXTAFTER returns #N/A. Wrap the formula in IFERROR to show a blank or a message:

=IFERROR(MID(A2,FIND("@",A2)+1,LEN(A2)),"Check email")

Rows that say "Check email" are the ones to fix by hand. To check that each cell has exactly one @, count them with LEN and SUBSTITUTE: =LEN(A2)-LEN(SUBSTITUTE(A2,"@","")) returns 1 for a valid address. The guide to counting characters and words explains this trick.

Practise it#

Splitting text at a character is one of the most useful text skills for data work. Practise TEXTBEFORE and TEXTAFTER in a live spreadsheet: you type the formula and it checks the result instantly. Then practise LEFT, RIGHT, MID and LEN so you can do the same in any version of Excel.