Extract usernames from E-mail IDs [using LEFT and FIND formulas in Excel]

Today we will learn to use Excel’s LEFT and FIND formulas. But what fun it is to learn a new formula on a Tuesday?

So, we will actually learn to use these formulas to solve the problem: “extract the username from an email ID”

How is an email ID structured?

Any email ID contains 2 parts – user name and domain name.

For eg. in my email id – chandoo.d@gmail.com – chandoo.d is user name and gmail.com is domain.

So how do we get the user name out?

As you can see, username always starts at left and goes up to the symbol “@”. So, If we write a formula to fetch all the characters up to “@” symbol, it will get us the user name.

This is where LEFT() and FIND() formulas enter the scene.

What does Excel LEFT formula do?

Excel LEFT formula will let you cut a portion of text from left. For eg. =LEFT("Long",2) will give you Lo. (syntax and examples)

So, to get the email username, we need to get all the letters in the left of email ID up to the location of “@” symbol. And how do we find the position of a symbol in a text?

We use FIND formula.

FIND formula gives the location of one text in another. For eg. =FIND("do", "chandoo") will give us 5 (the location of “do” in “chandoo”).

FIND will throw an error (#VALUE!) if the text you are trying to find is not available. For eg. =FIND("peace", "world") will throw #VALUE!

(syntax and examples)

Armed with these 2 formulas, now let us get that user name out of email ID

Assuming cell A1 has the email id, the formula for getting user name is =LEFT(A1,FIND("@",A1)-1)

We have to use -1 as find actually tells the position of “@” and we need all the letters up to “@”, but not “@”.

This is how it works:

Your homework:

• How would you extract the domain out of email ID? (Hint: there is a right formula for everything)

Use comments to write your answers. Don’t cheat.

Learn more excel formulas:

• 51 Excel Formulas in Plain English – Syntax, Examples and Explanation
• Excel Formulas & Working with Text
• Excel Formula Examples & Tutorials
• Learn Excel Formulas using my e-book – it is in a fun format & easy to understand