In this article we will show how to extract names from email addresses in google sheets in just a few simple steps. Simply follow the steps below:
Extract Name From Email Address in Google Sheets
1. Identify the Cell Containing the Email
For our example, we have two columns: Column A for the Name and Column B for the email.
The cells containing the emails in Column B start at B2. We then set Column C as the column for the names that we want to extract from the emails in Column B.
2. Identify the Username Format in the Email Address
Email usernames (the part to the left of the @) do not have a standard syntax that is uniformly applied across different services.
However, certain hosts such as companies and schools often use standardized formatting across their organization.
For instance, they may use periods and underscores to separate first and last names. Some examples of this are:
firstname.lastname@company.com
firstname_lastname@company.com
These two combinations are included in the example below:
3. Insert Formula to Extract The Name
The formula that we will use to extract the name from the emails is the following:
=PROPER(SUBSTITUTE(SUBSTITUTE(LEFT(cell, FIND("@", cell) - 1), ".", " "), "_", " "))
Where you replace cell with the reference to the cell containing the email address. In our example we will change “cell” to B2 in the formula.
=PROPER(SUBSTITUTE(SUBSTITUTE(LEFT(B2, FIND("@", B2) - 1), ".", " "), "_", " "))
4 Press Enter
You will now see the name extracted from the email address, properly formatted as well!
5. Apply the Formula to Entire Column
In order to extract the domain from all of the emails in the column there are two methods you can follow:
Click on the cell and double-click the round blue dot to autofill the column with the formula.
Click on the cell and Click-drag the round blue dot in the lower-right corner of the cell.
Either way, the results will be the same:
Name extracted from email address
You have now successfully extracted a name from an email in Google Sheets!
FAQs
What if the last name comes first instead of the first name?
The last name appearing first before the first name in email addresses are less common, but the solution can be pretty straightforward: instead of a space, we insert a comma to denote that the last name comes first. The formula can be modified to:
=PROPER(SUBSTITUTE(SUBSTITUTE(LEFT(cell, FIND("@", cell) - 1), ".", ", "), "_", ", "))
Where you replace the cell with the reference to the cell containing the email address. The result is:
What if the first name is just an initial?
Some email addresses are formatted to only have first name initials. If the first name initial is directly followed by the last name without a separator, the formula you can use is:
=PROPER(LEFT(cell,1) & ". " & REPLACE(LEFT(cell, FIND("@", cell) - 1),1,1,""))
Where you replace the cell with the reference to the cell containing the email address. You can see the result below:
If a separator such as a dot or an underscore is present, just modify the formula a little:
=PROPER(LEFT(cell,1) & ". " & REPLACE(LEFT(B7, FIND("@", cell) - 1),1,2,""))
Where you replace the cell with the reference to the cell containing the email address:
Recommended articles
More resources
