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:
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.
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:
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), ".", " "), "_", " "))
You will now see the name extracted from the email address, properly formatted as well!
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!
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:
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:
If you enjoyed this article, you might also like some of the articles below:
Email .CSV Files to Google Sheets
Save hours on repetitive and tedious work. Lido is a new spreadsheet that connects your spreadsheets, forms, PDFs, and email inbox.
.png)
