How to rename every URL on a column to share the same name in Google Spreadsheets?

user_17101540 0
Viewed 219

Suppose I have 10 youtube link & 700 stackoverlow link on the same column A. What to do to make that entire column all have the name 'My Link'. So on an empty cell, when I paste a link/url it automatically names itself 'My Link'.

I have tried searching for the result couple of time and stumbled upon =HYPERLINK(A1, "RECEIPT") & =ARRAYFORMULA(HYPERLINK(F1:F500, "RECEIPT")). I do not wish to to create a seperate colume where it says 'My Link' while another colume to paste the links on. I tried =HYPERLINK($A1, "My Link") and dragged down to copy the formula on the cell below but that gave Error because my links started from A2. Once I change it to =HYPERLINK($A2, "My Link") it works but every url moves up 1 cell above which doesnt properly reflect the corresping cell data. Not sure if my explanation was understandable or not.

Again I need the formula to impact the entire column A with the URLs without relying on a transfer to a different column. Can someone help?

google-sheets
1 Answers

The only way to change what's in the column without using a helper column would be to use a script, which is probably overkill.

If you are OK with using a temporary helper column you could do it like this:

  1. In cell B1, put the formula ="=HYPERLINK("""&A1&""", ""My Link"")"
  2. Copy this formula down to the end of your URLs (grab the small black box in the bottom right corner and drag down)
  3. Select all the cells in column B and copy (ctrl/cmd + c, or right-click and 'copy')
  4. Right-click in cell A1 and choose 'Paste Special' -> 'Paste values only'
  5. Select column A (by clicking the 'A' at the top), then on the menu bar choose 'Format' -> 'Automatic'
  6. You can then delete everything in column B

Edit: if you are OK with using a helper column, this would be much easier.

  1. URLS in column A
  2. =HYPERLINK(A1, "My Link") in B1 and copy down.
  3. Hide column A
user_110538260
Related