15

I have a column of cells in a Google Spreadsheet with values such as:

37683
36583
38637
32391

What I would like to do is generate a hyperlink for each cell like:

http://mywebsite.com?id=xxxxx

...where xxxxx is the value of the cell. I know the format of a hyperlink in a Google spreadsheet and could do the job manually, I'm just wondering if I can generate these using a simple find/replace, or whether it will need scripting.

The new hyperlink can either go into the original cell, or a new cell can be created.

Any ideas?

23

Assuming your values are in column A, you can do this in column B:

=HYPERLINK(CONCATENATE("http://mywebsite.com?id=",A1);"link text")

Then you can auto-fill down the rest of the column.

If you want the text of the link to be the same as the id you're linking, that's as simple as

=HYPERLINK(CONCATENATE("http://mywebsite.com?id=",A1);A1)
  • @AlEverett now how do you create that link in the same cell as the value? And keep the value as the text? – Kirill Fuchs Oct 12 '13 at 15:33
  • @Kirill: Copy / Paste values – ale Oct 20 '15 at 23:26
  • I realize this question is very old, but current Google Sheets (in 2018) does not create the link unless the URL is a literal string or cell reference. Using a formula to create the URL string will not work. You need to create the URL with a formula in one cell, then use the HYPERLINK function in another cell, referencing the created URL. Cell B1: ="http://mywebsite.com?id=" & A1 Cell C1: =HYPERLINK(B1, "link text") or =HYPERLINK(B1) – Neil Monroe Jul 6 '18 at 17:59
0
    Column A   Column B  Column C
  1    id's      Names
  2   37683      name 1
  3   36583      name 2
  4   38637      name 3
  5   32391      name 4

Pasting the below into C1 will generate text (from column B) with hyperlink attached that combines the web address (http://mywebsite.com?id=) with the id's (from column A). Change "Column Title" in the formula to whatever you want column C to be named in C1.

={"Column Title";ARRAYFORMULA(IF(ISBLANK(A2:A),,(HYPERLINK(("http://mywebsite.com?id="&A2:A),B2:B))))}

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.