Are you tired of manually extracting usernames from email addresses in Google Sheets? Well, you’re in luck! In this article, I will show you a simple and efficient way to accomplish this task using regular expressions (regex).
Understanding the Email Address Structure
Before we dive into the formulas, let’s quickly review the structure of an email address. An email address is composed of three parts: the username (or local_part), followed by the « @ » symbol, and finally the user domain.
Extracting the Username with REGEXREPLACE
One way to extract the username from an email address is by using the REGEXREPLACE formula. This formula allows you to replace specific patterns within a cell. In our case, we want to replace the second and third parts of the email address with a space, leaving us with just the username.
Here is the regex formula you can use:
=REGEXREPLACE(A1,"@(.*)","")By applying this formula to a cell, you will extract the username from the corresponding email address.
Extracting Username from Multiple Email Addresses
If you have a column with multiple email addresses and want to extract the usernames for all of them, you can use the same REGEXREPLACE formula with the ArrayFormula function. This will apply the formula to the entire column.
=ArrayFormula(REGEXREPLACE(A1:A,"@(.*)",""))This way, you can effortlessly extract usernames from a whole range of email addresses.
Alternative Formula: Left and Find Combination
If you prefer an alternative method to extract the username, you can use a combination of the LEFT and FIND functions. This formula will also yield the desired result.
=LEFT(A1,FIND("@",A1)-1)Similarly, you can apply this formula to a range of email addresses using ArrayFormula.
=ArrayFormula(LEFT(A1:A,FIND("@",A1:A)-1))Extracting Usernames from a BCC or CC List
As a bonus, I will share an awesome formula to extract usernames from a long list of email addresses that are comma-separated in a cell. This situation often arises when you have copied the list from the BCC or CC field of an email.
Here is the formula:
=PROPER(JOIN(", ",ArrayFormula(LEFT(ArrayFormula(TRIM(SPLIT(A1,","))), FIND("@",ArrayFormula(TRIM(SPLIT(A1,","))))-1))))In this formula, we utilize additional functions like PROPER, TRIM, SPLIT, and JOIN to extract and format the usernames from the comma-separated list.
And there you have it! You now know how to extract usernames from email addresses with ease. These formulas will save you valuable time and effort. So go ahead and give them a try in your Google Sheets.
I hope you find these formulas useful. Although I personally haven’t encountered many situations requiring username extraction from email addresses, I know many people are looking for solutions to this problem. If you want to explore more Google Sheets tips and tricks, be sure to visit Crawlan.com.
Happy username extracting!
