My colleague has a spreadsheet full of links. She copy/pasted them from a variety of different sources.

For a number of reasons, I want to convert these from just plain copy/pasted links to HYPERLINK formula cells.

Is there any way to do this?


Here's how I approached this problem. It requires some HTML/JS related knowledge, but I will try to post every step as detailed as possible.

Google Spreadsheet files can be downloaded as HTML pages.

  1. Copy the column/cells with the rich text links to a new sheet (for readability).

  2. From File > Download as > Web page (.html, zipped).

  3. Unzip the archive and you will get an .html file for every sheet you have in that document.
  4. Choose the sheet you have newly created in first step and open it in Google Chrome*.
  5. Use Ctrl + Shift + C on Windows or Cmd+Opt+C on Mac, to open the DevTools window.

  6. When the DevTools Window is opened look for the Console tab and paste the following:

let cells = document.getElementsByTagName('td');

for (let i = 0; i < cells.length; i++) {
    let links = cells[i].getElementsByTagName('a');
    //we are looping through all the links 
    for (let k = 0; k < links.length; k++) {
        //This is to make sure a link exists in the cell
        if (typeof links[k] !== 'undefined') {
            //We are going to add every link location and text as a new cell in that row
            let newCellLocation = cells[i].parentNode.insertCell(2);
            let newCellText = cells[i].parentNode.insertCell(3);
            newCellLocation.innerHTML = links[k].href;
            newCellText.innerHTML = links[k].text;

let thead = document.getElementsByTagName('thead')[0];

let table = document.getElementsByTagName('table')[0];
table.style.tableLayout = 'auto';

This will extend the current table by adding two cells (one for the link location and one for the link name) for each link that was in the original cell.

After that you can copy pate the newly created columns and perform your modification of the data: convert it to hyperlink or whatever.

*I have only tested this in Google Chrome, but this is javascript, so it should be correctly executed in Firefox's Developers Console, too.

  • Worked perfectly for a project we were doing. Had over 850+ records we needed to extract. – HPWD Aug 18 '19 at 21:53
  • @HPWD I'm glad it was helpful to you. Were the steps easy to follow? – Ivanka Todorova Aug 19 '19 at 11:39
  • They were prefect. – HPWD Aug 19 '19 at 11:40
  • I am a web developer so I’m quite comfortable working in Chrome’s Dev Tools. :) – HPWD Aug 19 '19 at 11:40
  • 1
    Here it is a six weeks later and I'm back to run this code snippet again! Very thankful for this! Pure brilliance. – HPWD Oct 20 '19 at 14:45

This can only be done manually.

When a link is pasted into a spreadsheet cell as a rich text, neither spreadsheet formulas nor script functions can access its URL. One can only get the link text, for example using =A1&"" in the spreadsheet, or getValues in a script.

For this reason, pasting links into spreadsheet cells as rich text should be strongly discouraged.

(Tangentially related: Extract the link text and URL from a hyperlinked cell).

  • See Ivanka Todorova's answer. – HPWD Aug 18 '19 at 21:54

If the cells all contain ".com" or "www" or "http" etc. I just do a simple find and replace.

  • Find "www" -> Replace with "www"
  • Search range or column etc.
  • Replace all
  • 1
    Just great! This is not only the simplest and most direct solution, but also gives you finer control: what links to convert, regex, case, inside/outside formula, in what range/sheets, entire cell contents, etc. – vstepaniuk Jan 11 at 22:05
  • Awesome thanks! – Raymond Tran Jan 11 at 23:13

You can create an easy macro for this

  • Go Tools -> Macro -> Record macro
  • Click Save and give this macro a name
  • Click EDIT SCRIPT or Tools -> Script editor to open the script editor
  • Replace the content of this macro function with the following code:
  var spreadsheet = SpreadsheetApp.getActive();
  var values = spreadsheet.getActiveRange().getValues();
  • Save this document (Ctrl+S)
  • Go back to the spreadsheet, select a range with text URLs and run the just created macro from Tools -> Macros

    (You will need to give permission to execute macros in this sheet).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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