Google-sheets – Generating Google Spreadsheet Links from cell contents

google sheets

You previously answered a part of my question, but I am looking to modify this behavior in multiple rows of column A so I don't want to manually correct it.

In A2 I have:

/mylist/groceries/index.html

In B2 I use concatenate

=HYPERLINK(CONCATENATE("https//mylife.com",A2),A2)

This creates the link I need, and returns the same text that is in A2, but I want just the first portion without the/index.html to be viewable.

/mylist/groceries

I do not want the "/index.html" to show

Best Answer

Clear Column B entirely, then place the following array formula into cell B2:

=ArrayFormula(IF(A2:A="",,HYPERLINK("https//mylife.com"&A2:A,REGEXREPLACE(A2:A,"/index.html",""))))

This should process everything in Column A as you would like.

Related Solutions

Google Sheets – Generate Hyperlinks from Cell Contents

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)

Google Sheets – Make All Cells in Column Links from Values

I wasn't able to reproduce your results. As a matter of fact, it worked perfectly.

enter image description here

What you tried to do is most probably the following:

In A1 you typed in =HYPERLINK(CONCATENATE("http://www.example.com/id/",A1);A1) and this yields an error of coarse.

Update

If you really want to get the result in A1, then you need to use a script.

Code

// global
var ss = SpreadsheetApp.getActiveSpreadsheet();

function onOpen() {
  var menu = [{name: "create URL", functionName: "createURL"}];
  ss.addMenu("URL", menu);
}

function onEdit(e) {
  var activeRange = e.source.getActiveRange();

  if(activeRange.getColumn() == 1) { 
    if(e.value != "") { 
      activeRange.setValue('=HYPERLINK("http://www.example.com/id/'+e.value+'","'+e.value+'")');
    }
  }
}

function createURL() {
  var aCell = ss.getActiveCell(), value = aCell.getValue();
  aCell.setValue('=HYPERLINK("http://www.example.com/id/'+value+'","'+value+'")');  
}

Explained:

The e.value will retrieve the cells value (only applicable a cell). The setValue() will add the concatenated string into the getActiveRange(). All is only executed when e.value contains something and the active range is in column A.

I've created an extra menu option as well, to be able access the script this way.

Example:

I've created an example file for you: onEdit URL builder
Add this script via Tools>Script editor, into the script editor. Press the "bug" button and you can use the script.