How to convert non-clickable links into clickable in Google Sheets?

Question

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?

Answer 1

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

Answer 2

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];
thead.remove();

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.

Answer 3

Even easier is to use Google Sheet's "hyperlink" function.

Suppose the text for your URLs starts in cell X2. In cell Y2, type =hyperlink(x2) and copy that formula to each row of Column Y for which you have a URL in column X.

See also https://support.google.com/docs/answer/3093313

Answer 4

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).

Answer 5

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();
  spreadsheet.getActiveRange().setValues(values);

• 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).

Answer 6

The answer by @ivanka-todorova worked for me on some smaller data sets, but given a spreadsheet with thousands and thousands of rows, I had to modify the javascript code to make sure the browser doesn't hang while processing everything:

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') {
            cells[i].innerHTML = links[k].href;
        }
    } 
}

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

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

Instead of allocating new cells, the above script entirely replaces cells with links in them with the href link itself (thus destroying the link text).

Answer 7

The simplest method:

1. Save the Excel as a CSV in Google Drive.
2. Right click on file in Google Drive, 'Open with...', select 'Google Sheets'
3. In Google Sheets, select File → Download → Microsoft Excel (*.xlsx)

Now when you open the downloaded file in Excel, all the cells with URLs work as hyperlinks.

Answer 8

This often happens because the original content of a cell (the link) was copied from a source like a web browser that puts formatted text on the clipboard. Google sheets only interprets pasted text as a hyperlink if it is pasted as PLAIN TEXT, not formatted text. It's difficult to convince Sheets to change its mind if it was originally pasted as formatted text.

To fix a "non-clickable link" cell, erase it and replace it with the PLAIN TEXT version of the content; then it should be clickable.

The trick is to make sure that your system clipboard has ONLY plain-text content before pasting, which means you can't copy it from a source that supports text formatting (like a web page or any app that supports formatting). You have to copy the links from a PLAIN TEXT source like Windows Notepad.

Here's how to fix existing cells in Google Sheets that are not clickable:

1. In your spreadsheet, Select a contiguous range of "non-clickable" cells.
2. Copy the cells to your system clipboard.
3. Hit Backspace to clear the selected cells (this preserves any cell formatting).
4. Open a PLAIN TEXT editor like Windows Notepad, and Paste.
5. Re-select all the text you just pasted, and Copy; now your clipboard has only the plain-text of the original content from the spreadsheet.
6. Go back to your spreadsheet and Paste, replacing the cells in your original selection with the plain-text version.