You can verify email addresses in Google Sheets using two methods depending on your requirements: the ISEMAIL function or People Chips.
Use ISEMAIL to verify any email address, whereas People Chips are used to verify email addresses in a cell range already in your Google Contacts.
Here, we will focus on the ISEMAIL function, while covering People Chips in a separate tutorial.
In addition to ISEMAIL, we will also touch on the email validation aspect of People Chips.
Syntax of the ISEMAIL Function in Google Sheets
Syntax:
ISEMAIL(value)Where value is the value to verify for an email address. The formula will return either TRUE or FALSE.
According to Google Documentation, the ISEMAIL function can verify a variety of top-level domains (TLDs), including:
Generic Top-Level Domains (gTLDs):
.com | .net | .org | .info | .biz | .tv | .co
Country Code Top-Level Domains (ccTLDs):
.us | .uk | .ca | .in | .au
Sponsored Top-Level Domains (sTLDs):
.aero | .coop | .museum | .edu
Note that this list does not include all top-level domains.
If an email is flagged as FALSE by ISEMAIL, it may use a top-level domain that isn’t on the supported list of TLDs or may not follow the standard email address structure.
Verifying Email Addresses with the ISEMAIL Function
Assume the cell range A2:A20 contains a list of email addresses. To verify them, use the following formula in cell B2:
=ISEMAIL(A2)Then drag the fill handle from B2 down to B20.
Check for FALSE to identify values that are either not email addresses or not in the supported list of TLDs recognized by ISEMAIL.
Validate Email Addresses with the People Chips
The ISEMAIL function checks whether an email address is formatted correctly according to standard email syntax rules. However, it doesn’t guarantee that the email address is deliverable or belongs to a real person. The email list used in the above test is a clear example of this.
The solution is to use People Chips. You can enter all your ‘valid’ contacts into Google Contacts and then apply People Chips to a list of email addresses in Google Sheets to check if they are deliverable addresses within your contact list.
Here are the steps to follow, aside from setting up Google Contacts:
- Assume A2:A10 contains a list of email addresses you want to verify.
- Select the range A2:A10.
- Click Insert > Smart Chips > Convert to People Chip.
Check the values in A2:A10. Google Sheets will validate the email addresses against your contact list, highlighting valid addresses with People Chips while leaving other values unchanged.
Once you have finished removing or correcting non-validated email addresses, remove the People Chips by selecting the range again, right-clicking, and selecting Smart Chips > Remove Smart Chips.
