36

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?

Mark Ireland
  • 541
  • 1
  • 5
  • 9

2 Answers2

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)
ale
  • 52,972
  • 42
  • 165
  • 314
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))))}

Galerion
  • 11
  • 1