Google Sheets – Handling Long URLs

google sheets

I have a google sheet that generates URLs from the values in cells. When the generated URL is short, the text produced is blue, underlined, and functions as a link to the URL. When the generated URL is long, the text produced is black and not a link.

URL that is formatted as a link:
http://www.google.com/search?q=Pax6+gene

URL that is not formatted as a link:
https://pubmed.ncbi.nlm.nih.gov/?term=Pax6+AND+%28%28%22heart+development%22%5BAll+Fields%5D+OR+%22cardiac+development%22%5BAll+Fields%5D+OR+%22cardiogenesis%22%5BAll+Fields%5D+OR+%22cardiac+morphogenesis%22%5BAll+Fields%5D+OR+%22heart+morphogenesis%22%5BAll+Fields%5D+OR+%22second+heart+field%22%5BAll+Fields%5D+OR+%22outflow+tract+development%22%5BAll+Fields%5D+OR+%22cardiac+crescent%22%5BAll+Fields%5D+OR+%22epicardial+development%22%5BAll+Fields%5D+OR+%22first+heart+field%22%5BAll+Fields%5D+OR+%22cardiac+organogenesis%22%5BAll+Fields%5D+OR+%22oft+development%22%5BAll+Fields%5D+OR+%22heart+organogenesis%22%5BAll+Fields%5D+OR+%22aortic+valve+development%22%5BAll+Fields%5D+OR+%22epicardium+development%22%5BAll+Fields%5D+OR+%22endocardial+development%22%5BAll+Fields%5D+OR+%22endocardium+development%22%5BAll+Fields%5D+OR+%22mitral+valve+development%22%5BAll+Fields%5D+OR+%22inflow+tract+development%22%5BAll+Fields%5D+OR+%22pulmonary+valve+development%22%5BAll+Fields%5D+OR+%22tricuspid+valve+development%22%5BAll+Fields%5D+OR+%22pulmonic+valve+development%22%5BAll+Fields%5D+OR+%22pro-epicardium+development%22%5BAll+Fields%5D+OR+%22pro-epicardial+development%22%5BAll+Fields%5D+OR+%22ift+development%22%5BAll+Fields%5D%29+OR+%28%22congenital+heart+disease%22%5BAll+Fields%5D+AND+%22development%22%5BAll+Fields%5D%29%29&sort=date&size=200

The long URLs are generated using this code:

="https://pubmed.ncbi.nlm.nih.gov/?term="&A2&"+AND+%28%28%22heart+development%22%5BAll+Fields%5D+OR+%22cardiac+development%22%5BAll+Fields%5D+OR+%22cardiogenesis%22%5BAll+Fields%5D+OR+%22cardiac+morphogenesis%22%5BAll+Fields%5D+OR+%22heart+morphogenesis%22%5BAll+Fields%5D+OR+%22second+heart+field%22%5BAll+Fields%5D+OR+%22outflow+tract+development%22%5BAll+Fields%5D+OR+%22cardiac+crescent%22%5BAll+Fields%5D+OR+%22epicardial+development%22%5BAll+Fields%5D+OR+%22first+heart+field%22%5BAll+Fields%5D+OR+%22cardiac+organogenesis%22%5BAll+Fields%5D+OR+%22oft+development%22%5BAll+Fields%5D+OR+%22heart+organogenesis%22%5BAll+Fields%5D+OR+%22aortic+valve+development%22%5BAll+Fields%5D+OR+%22epicardium+development%22%5BAll+Fields%5D+OR+%22endocardial+development%22%5BAll+Fields%5D+OR+%22endocardium+development%22%5BAll+Fields%5D+OR+%22mitral+valve+development%22%5BAll+Fields%5D+OR+%22inflow+tract+development%22%5BAll+Fields%5D+OR+%22pulmonary+valve+development%22%5BAll+Fields%5D+OR+%22tricuspid+valve+development%22%5BAll+Fields%5D+OR+%22pulmonic+valve+development%22%5BAll+Fields%5D+OR+%22pro-epicardium+development%22%5BAll+Fields%5D+OR+%22pro-epicardial+development%22%5BAll+Fields%5D+OR+%22ift+development%22%5BAll+Fields%5D%29+OR+%28%22congenital+heart+disease%22%5BAll+Fields%5D+AND+%22development%22%5BAll+Fields%5D%29%29&sort=date&size=200"

Is there a way to change this behavior?

Best Answer

You want to take a url and create a hyperlink in Google Sheets. Your urls vary from short (@40 characters) to very long (@1400 characters).

The HYPERLINK() function will create the hyperlinked-url. It is a built-in function. It should be noted that links can also be created manually from the Main menu - Insert>Insert link.

The syntax is HYPERLINK(url,[link label]) Doc ref.

The link label is optional. In the examples below, there are four examples of using (or not using) the link label.

HYPERLINK samples

sample

Note (18 Nov 2020):
I had meant to mention that the hyperlink can also be created with an apps script using setformula(). I haven't included the script here, but happy to add if anyone would like it.

Related Topic