Extract Username from Email Address Using Regex in Google Sheets

0
79
Extract Username from Email Address Using Regex

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.

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

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

Extract Username from Email Address [BCC or CC List]

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.

left and find combination to extract username from email id in google sheets

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.

LEAVE A REPLY

Please enter your comment!
Please enter your name here