25

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?

29

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)
| improve this answer | |
  • 2
    @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))))}

| improve this answer | |

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