1

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?

2
  • 2
    generates URLs from the values in cells Would you please edit your question to explain how hyperlinks are generating. I tested your data using several methods and can recreate your outcome only when pasting the long url directly into a cell or formula bar. Otherwise the following created blue hyperlinks for the long url: SETFORMULA(), HYPERLINK() (plain text url, separate label; url as label and no label) and Insert>Insert link using plain text url. On a tangent, have you considered that this could be a browser issue (FWIW, I'm using Chrome on Win10)?
    – Tedinoz
    Nov 12 '20 at 23:57
  • 1
    Wrapping my formula in hyperlink(...) solved the problem. @Tedinoz, If you make this an answer I will accept it. Thanks.
    – Josh
    Nov 13 '20 at 21:05
3

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.

0

I tested this and it appears that the automatic conversion to hyperlinks is capped at just over 1000 characters.

But you can create the hyperlink manually:

  1. Copying the url
  2. Right click on the cell
  3. choose insert hyperlink
  4. paste url

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.