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:
where value is the value to be verified as an email address.
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.
In this example I’ve used a nested IF statement to test the data in column A for both URLs and email addresses.