Separate Email addresses to usernames and domains in Excel

Normally, the Email address is consisted of user name, @ symbol and domain name, but, sometimes, you may need to split the Email address into separate username and domain name as below screenshot shown, this article, I will introduce some formulas for splitting the Email addresses in Excel.

• Separate Email addresses to usernames and domain names with formulas

Separate Email addresses to usernames and domain names with formulas

Extract usernames from Email addresses:

In Excel, the LEFT and FIND functions can help you to extract the usernames, the generic syntax is:

• email: The Email address or cell contains the Email address that you want to split.

Please copy or enter the following formula into a blank cell where you want to get the result:

And then, drag the fill handle down to other cells that you want to fill this formula, and all the usernames have been extracted as below screenshot shown:

Explanation of the formula:

FIND("@",A2)-1: The FIND function will return the position of the character @ in cell A2, and subtracting 1 means to exclude the @ character. And it will get the result: 10.

LEFT(A2,FIND("@",A2)-1)=LEFT(A2, 10): The LEFT function will extract 10 characters from the left side od cell A2.

Extract domain names from Email addresses:

To extract the domain names, the RIGHT, LEN and FIND functions can do you a favor, the generic syntax is:

• email: The Email address or cell contains the Email address that you want to split.

Please apply the below formula into a blank cell:

And then, drag the fill handle down to the cells you want to apply this formula, and all domain names have been extracted at once, see screenshot:

Explanation of the formula:

FIND("@",A2): The FIND function will return the position of the character @ in cell A2, and it will get the result: 11.

LEN(A2):This LEN function is used to get the number of characters in cell A2. It will get the number 20.

LEN(A2)-FIND("@",A2)=20-11: Subtract the position of the @ character from the total length of the text string in cell A2 to get the number of character after the @ character. This part formula is recognized as the num_chars argument of the RIGHT function.

RIGHT(A2,LEN(A2)-FIND("@",A2))=RIGHT(A2, 9): At last, this RIGHT function is used to extract 9 characters from the right side of the cell A2.

Relative functions used:

More articles: