Convert Non-Clickable Links into Clickable in Google Sheets

I usually do keyword research for my blog using a couple of SEO tools.

Once, when I exported a list of keywords in a .csv file and opened it in Google Sheets, I noticed a column full of non-clickable links.

I wanted those links to be clickable for convenience.

Sure, you can double-click inside the cell and press enter to make the link clickable, but I had 35,000 links; doing that 35,000 times would obviously be a tedious task.

Then I discovered a formula that can convert all those non-clickable links into clickable ones.

In this article, I will show you how to use the formula so that you can convert the links too.

Also read: How to Insert a File into Google Sheets

To Convert Non-Clickable Links into Clickable in Google Sheets:

Step 1: Add a new column next to the column containing the non-clickable links

The first step is to open the spreadsheet and then add a new column next to the column containing the non-clickable links.

We will first create a new column, then use a formula to convert the non-clickable links to clickable, place them in the new column, and then delete the original column.

So after you create a new column for the clickable links, proceed to the next step.

Step 2: Double-click inside the first empty cell of the new column

Now double-click inside the first cell of the new column so that we can paste the formula there.

Do note that you must click inside the first empty cell of the first column, if the first cell is the column heading than click inside the second cell.

The selected cell is from where the clickable link list will start.

Step 3: Paste the given formula

After you click in the cell use this formula:

=ArrayFormula(if(A2:A<>"",hyperlink(A2:A,A2:A),))

This formula will first check the cells in the column “A,” starting from cell A2.

If the cell is not empty, it will convert the non-clickable links to clickable ones. It will continue to do this until it finds an empty cell.

Do make sure to edit the column name and the cell name in the formula accordingly.

For example, my non-clickable links were in A and were starting from cell A2; hence, I have used the above formula and pasted it in cell B2.

Step 4: Press Enter

After you paste the formula, press the Enter key, and all your non-clickable links from the original column will be converted to clickable links and placed in the newly created column.