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 [email protected], 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:

  • neale_blackwood says:

    Don’t forget you can use Flash Fill for Excel 2013 and later versions.

  • Khushhal Togra says:

    Hi Sumit,

    i have written this function the pop up is showing “You’ve entered too many arguments for this function”

    Please help me
    My ID is [email protected]

    • Sumit Bansal says:

      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)?

  • Neeraj Agarwal says:

    Excellent!!!!!!

  • Jon Acampora says:

    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. 🙂

    • Sumit Bansal says:

      Thanks for sharing this Jon.. Definitely a good way to do this quickly 🙂

    • Victor Momoh says:

      I was just going to suggest this, only to see that Jon had already done that…My search for an original tip continues 🙂

  • ضياء شديد says:

    Great.

  • Dhiraj says:

    Nice

  • >