Well, we know that the domain name always comes after the @ sign in any email id. To extract domain name from email address we can use this fact. So there are two methods to do this.
Let’s check them out one by one.
Extract domain name from email using formula.
Generic Formula to Extract Domain Name
Email id: this is the text that represents an email id.
Let’s see an example to make things clear.
Example: Extract Domain Name From Email Ids
Here, I have this list of email ids. I want to extract domain names in a separate columns, while leaving the original text intact.
Apply above generic formula in cell B2 to extract domain name.
Copy down the cell B2. You’ll have your domain names extracted in column B.
How it works?
The idea is to get number of characters after @. Once we get it, we can use RIGHT function to domain name.
Let’s break above formula down to understand it.
FIND("@",A2): The find function returns the position of a given text in a string. Here it returns the position of @ in Name1@gmail.com, which is 6.
LEN(A2): it returns the total number of characters in a text. Name1@gmail.com has 15 characters in it.
Now the formula is simplified to RIGHT(A2,15-6) ? RIGHT(A2,9), which gives us our domain name gmail.com. Same things happens in all cells.
Use formula when this task is repetitive. Have a defined column for email ids for formula. Whenever you’ll change data in email column, all domains will be extracted automatically.
But if you have a one time task, then there is another method to do this. It is called text to column.
Use Text to Column to Extract Domain Name from Email
To extract domain name and user id in different columns, use this method. If you want the original data intact, i suggest you to use a copy of data.
It is done. The domain name and username is separated in two different columns. The original text will not have the domain name with it. And thats why i recommend to use a copy of original data.
Before using this method, make sure that you don’t have any data in columns where you want your text to be separated. Excel shows a warning if it finds any data in columns where data may get overridden. Its best to use a new sheet for this.
So yeah guys, this how you can extract domain name from email ids in excel. Both of the methods are useful in their context. If you want any other kind of solution, let me know in the comments section below.
How to Remove Text in Excel Starting From a Position
Split Numbers and Text from String in Excel
The VLOOKUP Function in Excel
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.