The ISURL function in Google Sheets is one of the Web functions (categorized under Web). Needless to say, the only purpose of this simple function is to check valid URLs.
The ISURL formula will return TRUE/FALSE values based on the value (supposed URL) provided as the input. If the formula returns a TRUE, then that the provided ‘value’ is a valid URL.
The entered URLs are normally linked (visually different from other values by blue color font and underline) in Google Sheets. Even if the URLs are not linked, the ISURL function can successfully check the validity of the URLs.
Syntax:
ISURL(value)
Argument:
value – The value (supposed URL) to be verified.
Formula Example and Explanation:
From the above examples, it’s clear that http
/ https
protocols and the www
subdomains are not required in all cases to validate (formula returns TRUE) a URL.
As per the Docs help center article, the below are the valid protocols in the ISURL function in Google Sheets.
- ftp
- http
- https
- gopher
- mailto
- news
- telnet
- aim
Also please note that the localhost
domain (please refer to cell A9 and the formula output in cell B9 above) does pass URL validation in Google Sheets. That means TLDs, i.e. .com, .org, .net., .gov, etc., are not a must to pass the URL validation/check.
How to Use the ISURL Function in Conditional Formatting in Google Sheets
To highlight valid URLs, we can use the ISURL function as a custom formula in Google Sheets Conditional Formatting rules.
To highlight URLs, as an example, I am considering the above same values in column A, I mean the range A2:A.
Custom ISURL Formula for Highlighting URLs in Sheets:
=isurl(A2)
Without much explanation, I hope, you can understand how to apply this custom ISURL formula in conditional formatting from the below screenshot.
Validate URLs in Data Validation in Google Sheets
I just want to allow valid URLs in a range in Google Sheets. How to do that?
Google Sheets Data Validation has a built-in rule to restrict entering invalid URLs in a cell or range. To apply this rule, do as follows.
- Select the range to validate URL entry.
- Go to Data > Data validation.
- Under Criteria drop-down, select “Text”.
- Select “Is a valid URL” from the next drop-down.
The below Data Validation settings are for the range A1:A10.
Check URLs in Multiple Rows and Hplerlink
Here is a bonus tip! To check URLs in several rows or columns, we can use ArrayFormula with ISURL as below.
Array Formula to test URLs in the range A2:A13.
=ArrayFormula(isurl(A2:A13))
To hyperlink valid URLs you can use the following combo formula (a combination of IF, ISURL, Hyperlink and ArrayFormula functions).
=ArrayFormula(if(isurl(A2:A13)=TRUE,hyperlink(A2:A13),))
Related Reading: