0
79

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 we can check 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 same. Here, I’m following the second approach.

First I’m going to use REGEXREPLACE formula for this purpose.

Here is the formula:

=REGEXREPLACE(A1,”@(.*)”,””)

This regex formula replaces the second and third part of the Email Address with Space. So you would left with the username only.

When you wan’t 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))

As a bonus I’m going to provide you 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 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.

SHARE
Previous articleHow to Use Trim Function With Split in Google Sheets

A technology enthusiast and addictive blogger who likes to travel and wish to mingle with different community of people from around the world.