How can I create a hyperlink in the middle of cell text using a formula?

Question

Is there any way to programmatically concatenate text (e.g. using formulas) that includes hyperlinked text?

I am looking for a way to automate creation of documents that have links within long text descriptions in cells, e.g. invoices.

In Google Sheets, the HYPERLINK function in Google Sheets returns hyperlink enabled text and the CONCATENATE function allows stringing together or appending various texts.

Formula	Result
`=HYPERLINK("https://stackoverflow.com", "Stack Overflow")`	Stack Overflow
`=CONCATENATE("SO: ", "https://", "stackoverflow.com")`	`SO: https://stackoverflow.com` (hyperlink not enabled)

When HYPERLINK is used within CONCATENATE, however, the hyperlinked text shows up as plain text.

Formula	Result
`=CONCATENATE("SO: ", hyperlink("https://stackoverflow.com", "Stack Overflow"))`	SO: Stack Overflow

Use of CONCATENATE as arg in HYPERLINK works as expected.

Formula	Result
`=HYPERLINK(CONCATENATE("https://", "stackoverflow.com"), "Stack Overflow")`	Stack Overflow

This is not the same question as https://stackoverflow.com/questions/8970391/how-can-i-create-a-hyperlink-in-the-middle-of-cell-text [How can I create a hyperlink in the middle of cell text?]. The OP in that question did not specify the need for a programmatic or fomula-based solution. The working answers also do not provide a programmatic or formula-based solution. — kaanchan, Nov 27 '21 at 18:00
Add google-apps-script tag since you're looking for programmatic answer. — TheMaster, Nov 28 '21 at 21:31

Mike Steelson · Answer 1 · 2021-12-03T15:05:53.087

You can set multiple hyperlinks (as many as you wish) with this script (not by formula)

function multipleHyperLinks() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A1').setRichTextValue(SpreadsheetApp.newRichTextValue()
    .setText("go to ... stackoverflow")
    .setLinkUrl(10, 23, "https://stackoverflow.com/search?tab=newest&q=google%20sheets")
  .build());
};

RichTextValueBuilder

setLinkUrl(startOffset, endOffset, linkUrl)

By the way, if you want to retrieve all the links, use

function getMultipleLinks() {
  var sh = SpreadsheetApp.getActive()
  var rng = sh.getRange('A1')
  var richTexts = rng.getRichTextValue().getRuns()
  richTexts.forEach(function(richText){
    Logger.log('|'+richText.getText() + '| = ' + richText.getLinkUrl())
  })
}

edit

Here is an automatic solution based on onEdit(e) function :

Type in a cell : foo <link1|text1> foo <link2|text2> foo

for instance : go to ... <https://stackoverflow.com/|stackoverflow>

add 'Edit:' at the beginning to reverse

// mike.steelson
// example
// go to <https://stackoverflow.com/|stackoverflow> and <https://www.amazon.fr/|amazon> here
// add Edit: at the beginning to reverse
function onEdit(event){
  var sh = event.source.getActiveSheet();
  var rng = event.source.getActiveRange();
  var value = rng.getValue()

  if (value.substring(0,5).toLowerCase()=='edit:'){
    var richTexts = rng.getRichTextValue().getRuns()
    var txt=''
    richTexts.forEach(function(richText){
      if (richText.getLinkUrl()==null){
        txt+=richText.getText()
      }else{
        txt+= '<' +  richText.getLinkUrl() + '|' + richText.getText() + '>'
      }
    })
    rng.setValue(txt)
  } else {
    var occ1 = ExtractAllRegex(value,'<([^>]+)>',0)
    var occ2 = ExtractAllRegex(value,'>([^<]+)<',0)
    occ2.push('')
    if (sh.getName()=='mySheet' && occ1.length>0){
      var richText = SpreadsheetApp.newRichTextValue()

      var txt = value.split('<')[0]
      for (var i=0; i<occ1.length; i++){
        var arr = occ1[i].replace('<','').replace('>','').split('|')
        txt += arr[1]
        txt += occ2[i].replace('<','').replace('>','')
      }
      txt += value.split('>')[value.split('>').length-1]
      richText.setText(txt)

      var start = value.split('<')[0].length
      for (var i=0; i<occ1.length; i++){
        var arr = occ1[i].replace('<','').replace('>','').split('|')
        var len = arr[1].length
        richText.setLinkUrl(start,start+len,arr[0])
        start += len + occ2[i].replace('<','').replace('>','').length
      }

      rng.setRichTextValue(richText.build())

    }
  }
}
function ExtractAllRegex(input, pattern,groupId) {
  return Array.from(input.matchAll(new RegExp(pattern,'g')), x=>x[groupId]);
}

I'm not sure how to test this or implement it. Can you suggest a starting point or provide steps for how to get it into a sheet (or any accessible location) and how to call it from a cell? — kaanchan, Dec 01 '21 at 03:01
unfortunately we cannot edit partial text by custom formula https://stackoverflow.com/questions/49287090/apply-multiple-font-colors-to-the-text-in-a-single-google-sheets- cell I will work on another friendly solution — Mike Steelson, Dec 01 '21 at 08:28
@MikeSteelson, the first section for this is what I've been looking for! I was wondering if there was an alternative way to hyperlink between sheets and their cell range. Example: `.setText("go to ... Sheet2") .setLinkUrl(10, 23, **sheet2.getRange('A1') **") .build());`... The edited code returns a link however would only like the code to go the sheet and not open a new tab. Thank you. — Stephanie, Nov 17 '22 at 22:01

score 1 · Answer 2 · answered Nov 27 '21 at 19:59

1

if this is your goal:

then this is possible to achieve only manually editing the cell (or with script). usage of any internal formula you think of will render it as "plain text" (eg. removing the hyperlink)

answered Nov 27 '21 at 19:59

player0

124,011
12
67
124

score 0 · Answer 3 · answered Nov 28 '21 at 04:15

Unfortunately, not in that way. Hyperlink (via formula) will always include the whole text in the cell, but I recommend you, if possible, to arrange the whole text in the invoice to leave the hyperlink in a cell, with all the paragraphs around.

it's an idea of how I would try it.

How can I create a hyperlink in the middle of cell text using a formula?

3 Answers3

edit

Linked

Related