How to separate email username and domain in a excel spreadsheet

These formulas can also be used to separate First name and Last name in excel spreadsheet. I actually face this problem of separating thousands of emails domain name early morning today at work. I was suppose to do a study on popular mailer domains of our email subscriber base. Extracting mailer domains from more than 40,000 emails addresses was simply inhuman so I tried to develop excel formula, finally come out with this. Here find the formula to separate email username and domain in a excel.

=RIGHT(A1,((LEN(A1)-FIND(“@”,A1))))    //this will give you mailer domain

=LEFT(A1,FIND(“@”,A1)-1)     //this will give you username

First we will understand FIND(“@”,A1). This can be use as =FIND(“@”,A1) standalone way. We are asking to count the position of “@” in cell A1. This formula always work/ count from left to right. For example if an email address is abc@gmail.com then, the position of “@” is 4 from left.

Now LEN(A1), this part of formula actually count the total character in cell A1.

Therefore, (LEN(A1)-FIND(“@”,A1)) is giving us the number of character the mailer-domain made of. Now the final step. The expression can be read as =RIGHT(A1,(number of character the mailer-domain made of)). In this formula we are asking spreadsheet to read the characters for mailer-domain, from right in cell A1. So we successfully separated mailer-domain.

Now a similar formula can be written for username. We have understand =FIND(“@”,A1) count the position of “@” in cell A1. Therefore we can make a formula =LEFT(A1,FIND(“@”,A1)) it actually reads from left the number of character till “@” in cell A1. But this also include the character “@” itself. To remove “@” we need to count 1 character less. Finally the formula to get username is =LEFT(A1,FIND(“@”,A1)-1).

These same sets of formulas can be use to separate any other couple of words, for example First name and last name. We just need to change the operator “@” as required. For name those formated as ARIJIT DUTTA, i.e. separate first name and last name by a “space”, we need a “space” as operator. The modified formulas will be.

=LEFT(A1,FIND(” “,A1)-1)    //this will give you the first name

=RIGHT(A1,((LEN(A1)-FIND(” “,A1))))    //this will give you the last name

Tell us, if you have any other ideas to use this formulas.