Extract Usernames from Email Ids in Excel [2 Methods]

A colleague asked me if she could quickly extract usernames from Email Ids. She had more than 1000 records and less than 2 minutes to do it.

In this tutorial, I will show you 2 methods to do this. And none of it would take more than a minute.

Method 1 – Using Text to Column

This is the easiest way if the data has a pattern. For example, in email id, there would always be a username, followed by ‘@’, and ends with the domain name.

The trick is to extract the text before the @ sign. Here is how you can do this:

1. Select all the email ids
2. Go to Data –> Data Tools –> Text to Columns
3. In the Text to Column Wizard
   • Step 1: Ensure Delimited is checked as the data description and click Next
     
   • Step 2: In Delimiters options, select Other, and type @ in the text field adjacent to it. Click Next
     
   • Step 3: In the Data preview you can see the data has been separated by username and domain name. Select the second column in Data preview (the one which has the domain name) and select Do not import Column option in Column data format. Also, you can select a Destination cell where the Usernames to be extracted
     
4. Click on Finish

This will give you the usernames from the email ids. Note that if you do not specify a destination cell in Step 3, the original data is overwritten with the extracted data (excel does warn you before overwriting).

This method is super fast and you can have the list in a couple of seconds. However, it is not dynamic. If you add a record or make any changes in existing email ids, you will have to do this again.

Related: 7 Amazing Things Excel Text to Columns Can Do For You

Method 2 – Using Excel Formulas

Excel Formulas has the benefit of making the results dynamic. With formulas, if you change the email ids, the result would update automatically.

Here is the formula you can use to do this:

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

There are 2 parts to it:

• FIND(“@”,A2,1) returns the position of @. In case of abc@gmail.com, it will return 4. Now we want to extract the text on the left of @, so we subtract 1 from this formula (which would return 3)
• LEFT(A2,FIND(“@”,A2,1)-1) extracts all the characters to the left of @

Whichever method you choose, it will not take you more than 2 minutes to do it.

Mission Accomplished 🙂

You May Also Like the Following Excel Tutorials:

• Analyze Each Character of a Text String.
• How to Extract a Substring in Excel (Using TEXT Formulas).
• Extract Data from Drop Down List Selection in Excel.
• Separate First and Last Name in Excel (Split Names Using Formulas)
• How to Combine First and Last Name in Excel