When you want to extract Username from email address, in Google Sheets there are multiple options. Here I’m using REGEXREPLACE formula to do this. Also, there is another variant using LEFT and FIND combination. That combination also you can experiment below.
An Email Address or Email ID has three parts. The first part is the username or local_part, then the @ symbol and finally the user domain.
Whatever formula you are going to use to extract Username from email address, you should consider the second part of the email address. That is the @ symbol. Based on this there are two options in front of you. You can either extract the username directly or replace the domain with space. The result would be the same. Here, I’m following the second approach.
Extract Username from Email Address in Google Sheets
First I’m going to use REGEXREPLACE formula for this purpose.
Regex Formula to Extract Username from Email Address
Here is the formula:
=REGEXREPLACE(A1,"@(.*)","")
This regex formula replaces the second and third part of the Email Address with Space. So you would be left with the username only.
When you want to extract email address from an entire column, you can use the above same formula but with ArrayFormula.
=ArrayFormula(REGEXREPLACE(A1:A,"@(.*)",""))
Left and Find Formula Combination to Extract Username from Email Address
You can use the below combination of Left and Find functions as an alternative to REGEXREPLACE.
=left(A1,find("@",A1)-1)
Here also you can use it with ArrayFormula.
=ArrayFormula(left(A1:A,find("@",A1:A)-1))
Extract Username from Email Address [BCC or CC List]
As a bonus, I’m going to provide you with another awesome formula. When you have a long list of email addresses, that is comma separated, in a cell, you may want to extract all the usernames at a time.
This situation may arise when you have received the list copied from BCC or CC of your Email. See the below image first.
And the formula here is;
=proper(join(", ",ArrayFormula(left(ArrayFormula(TRIM(split(A1,","))),
find("@",ArrayFormula(TRIM(split(A1,","))))-1))))
Here I’ve used a few more functions like Proper, Trim, Split and Join.
That’s all. Hope some of you will find the above formulas useful. For me, I’ve never faced such situations to extract users’ name from email IDs. But I know, there are many people who want to know how to do this extraction.
