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?

Improve this question
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
    Commented Nov 12, 2020 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
    Commented Nov 13, 2020 at 21:05

2 Answers 2

Reset to default
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.

Improve this answer
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
Improve this answer
1
  • Is there a way I don't have to do this manually for 100s of rows?
    – Killroy
    Commented Sep 20, 2022 at 7:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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