Validate Email Addresses and URLs in Google Sheets

In at least one previous post I’ve talked about data validation. This can be something of a challenge, and the regex expressions required to accurately test emails and URLs – two of the most common data types to validate – can be complex and hard to understand.

Thankfully, the newer version of Google Sheets has given us two new functions to help with this task; ISEMAIL and ISURL. Here’s the syntax:

ISEMAIL(value)

where value is the value to be verified as an email address.

ISURL(value)

where value is the value to be verified as a URL.

Either function will return TRUE if the validation succeeds and FALSE if not.

ISURL has some additional tricks up its sleeve:

  • Google Sheets automatically links the text entered into a cell if a valid URL is detected by ISURL. The function will return TRUE where this is the case.
  • The function doesn’t need a fully qualified URL; “http” and “www” can usually be omitted.
  • Various protocols are valid, including ftp, http, https, mailto, news, telnet, and aim.
Click image to enlarge

Click image to enlarge

In this example I’ve used a nested IF statement to test the data in column A for both URLs and email addresses.