20

I've got a document that's convenient to keep in a spreadsheet because of calculations, but I also have a significant amount of text that I'd like to keep in the same place. Some of that text involves hyperlinks, preferably in line with plain text, but my attempts at figuring out how to write a formula that prints the output from HYPERLINK() next to plain text have all resulted in parsing errors.

The result I'm looking for is

Google plain text

So far I've tried

=HYPERLINK("http://google.com", "Google") + plain text
=CONCATENATE(HYPERLINK("http://google.com", "Google"), plain text)

Google's documentation isn't leading me anywhere.

1

Since 2020-06-02 it is possible to have multiple links mixed with text - Google Sheets introduced me to this feature. This feature seems to be public (it is not some internal test), since I found it in official documentation:

Tip: You can add multiple links in a single cell in a spreadsheet. To add multiple links > in a cell, highlight the text you want to link. Then, at the top, click Insert link Insert link or press CTRL + k or ⌘ + k on your keyboard.

Adding link to text

Cell correctly renders text with the links. List of links will pop up when cell is selected:

Selected cell with links

Edit: One thing that does not work is preserving this links through expressions. For example i use =FILTER(main!A:M, main!B:B="favourites") to create filtered view. While link only cell displays properly, links mixed with text to not render correctly in this view.

| improve this answer | |
  • Very nice. Thanks for the update. – Holland Wilson Jun 7 at 13:38
11

No, a cell cannot contain a mix of text and links. Either the entire cell is a link or there is no link.

Keeping a document within a spreadsheet is rather unnatural. I suggest to consider one of the following:

  1. Embed the spreadsheet in a document of another kind, which allows rich text formatting and embedding of an iframe. E.g., a Google Site as described here.

  2. Use spreadsheet as data source from which the document is generated. You can keep HTML source (<a href='url'>text</a>, etc) in the spreadsheet along with the data, and produce a published version with a script: see HTML service.

| improve this answer | |
  • 1
    Getting away from the spreadsheet would be much more effort than it's worth. It's not super-important, but the question was interesting enough for me to want to poke around for an answer. I'll just stick the whole text for each of those cells in the anchor text, it will bother my perfectionism a little bit, and everything will work just fine. Thanks. :) – Holland Wilson Sep 3 '15 at 19:59
4

This is not possible.

From the documentation ("Add links in a spreadsheet"):

Please note: Like the other types of data and cell formatting, links in spreadsheets must be assigned to an entire cell, you cannot have just part of the text inside a cell be hyperlinked.

| improve this answer | |
1

This is possible--but I don't know how I did it.

When I typed formatted text followed by a URL into cell E2 in the spreadsheet shown below, it magically formatted in a way I didn't know Sheets did--but I've never been able to replicate that elsewhere.

URL shown in part of Google Sheets cell

This is an undoctored screenshot. (Here's a link to the Sheet--make your own copy.)

  • Hover over any part of the cell and the popup link appears.
  • It doesn't use HYPERLINK().
  • I did not underline or change the color of the text at the end of the cell.

WTF!? How did I do that?!

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.