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)

CodePudding user response：

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.

CodePudding user response：

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 sheets")
  .build());
};

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())
  })
}

By a combination of these two scripts, you can create a custom function to concatenate multiple cells with or without links and keep the right link to the right portion of text.