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.

Extract Usernames from Email Ids - Sample DataIn 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
      Extract Usernames from Email Ids - Step 1
    • Step 2: In Delimiters options, select Other, and type @ in the text field adjacent to it. Click Next
      Extract Usernames from Email Ids - Step 2
    • 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
      Extract Usernames from Email Ids - Step 3
  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:

Excel Ebook Subscribe

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Sumit Bansal
Hello there! I'm Sumit Bansal, founder of trumpexcel.com and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.

13 thoughts on “Extract Usernames from Email Ids in Excel [2 Methods]”

    • Hi Khushhal.. Thanks for commenting. The formula seems to be working fine on my system. I checked it for various versions of excel. Can you share the file or data set (in drop box or onedrive)?

      Reply
  1. Great tips Sumit! One other I thought of was to do a Find and Replace.
    Find: @*
    Replace:
    Leave the replace field blank. This will find all the “@” symbols and the asterisk symbol “*” is a wildcard that will include everything after the “@”. Replace it with nothing and you will be left with the username only.

    This will of course modify the original records, so you might want to make a copy of the data first. But I still think it can be accomplished in under 2 minutes. 🙂

    Reply

Leave a Comment