35

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?

Improve this question

2 Answers 2

Reset to default
47

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
5
  • 6
    @AlEverett now how do you create that link in the same cell as the value? And keep the value as the text?
    – Kirill Fuchs
    Commented Oct 12, 2013 at 15:33
  • @Kirill: Copy / Paste values
    – ale
    Commented Oct 20, 2015 at 23:26
  • 1
    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
    Commented Jul 6, 2018 at 17:59
  • 1
    Works fine for me in 2022.
    – Liam
    Commented Mar 26, 2022 at 21:37
  • Personally, I was able to do this (without needing different text for the link) using just the formula ="http://mywebsite.com?id="&A1 (with or without spaces around the &) – well, I was using a different URL fragment and had the relevant IDs in a different column, but the same logic applies. It also works as =CONCATENATE("http://mywebsite.com?id=",A1) (again, with or without spaces around the comma). Use whichever format you prefer. :)
    – V2Blast
    Commented Mar 1, 2023 at 17:49
1 
    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.