ISEMAIL Function: Verify Email Addresses in Google Sheets

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.

ISEMAIL Function Example in Google Sheets

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:

  1. Assume A2:A10 contains a list of email addresses you want to verify.
  2. Select the range A2:A10.
  3. 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.

Validating email addresses using People Chips feature in Google Sheets

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.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

How to Filter by Total in Google Sheets Pivot Tables

Google Sheets offers many tools to summarize and analyze data, but Pivot Tables are...

More like this

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.