"Collapsing" HYPERLINK Formula in Google Spreadsheets
Quick pro tip: If you need to convert hyperlink functions into linked text in Google Sheets, copy the cells and paste values, then paste formatting.
Suppose you have the following in Google Sheets.
| A | B | |
|---|---|---|
| 1 | =HYPERLINK("https://google.com", B1) |
Link Text |
The cell appears as a hyperlink like this: Link Text
Right now that cell is a formula, so if you copy and paste it somewhere else, its references will be updated. If you copy and paste values, you only get the unlinked "Link Text".
So if you have a long column of links defined using the hyperlink formula, how would you collapse it into hyperlinked text?
Copy the affected cells, then use Right Click > Paste Special > Paste Values. Then in those same cells use Right Click > Paste Special > Paste Formatting.
Bam.