Are you trying to extract the username from an email address in Google Sheets? Look no further! In this article, we’ll explore different methods to accomplish this task, using the REGEXREPLACE formula and a combination of LEFT and FIND functions. So, let’s get started!
The Anatomy of an Email Address
Before diving into the formulas, let’s briefly understand the structure of an email address. An email address consists of three parts: the username or local_part, the @ symbol, and the user domain.
Extracting Username with REGEXREPLACE
We’ll start by using the REGEXREPLACE formula to extract the username from an email address.
Regex Formula to Extract Username from Email Address
Here’s the formula you need:
=REGEXREPLACE(A1,"@(.*)","")
This regex formula replaces the second and third part of the email address with a space, leaving you with only the username.
If you want to extract email addresses from an entire column, you can use the same formula with ArrayFormula:
=ArrayFormula(REGEXREPLACE(A1:A,"@(.*)",""))
Alternative Method: Left and Find Combination
If you prefer an alternative to REGEXREPLACE, you can use the combination of LEFT and FIND functions to extract the username from an email address.
=LEFT(A1,FIND("@",A1)-1)
Similarly, this formula can also be used with ArrayFormula:
=ArrayFormula(LEFT(A1:A,FIND("@",A1:A)-1))
Extracting Username from Email Address [BCC or CC List]
As a bonus, here’s a formula to extract multiple usernames from a long list of email addresses separated by commas. This situation commonly occurs when you have copied a list from the BCC or CC field of your email.
- Use the below formula to extract the usernames:
=PROPER(JOIN(", ",ARRAYFORMULA(LEFT(ARRAYFORMULA(TRIM(SPLIT(A1,","))), FIND("@",ARRAYFORMULA(TRIM(SPLIT(A1,","))))-1))))
In this formula, we use additional functions like PROPER, TRIM, SPLIT, and JOIN to ensure the usernames are properly formatted and separated by commas.
That’s it! These formulas will help you effortlessly extract usernames from email addresses in Google Sheets. While I haven’t personally encountered a situation where I needed to extract usernames, I acknowledge that it can be a common requirement for many individuals.
If you found the above formulas useful or have any other tips and tricks, feel free to share them with us! Visit Crawlan.com for more valuable insights and resources.
Remember, extracting usernames from email addresses can be a breeze with Google Sheets and a little bit of know-how. Happy extracting!
