MrExcel.com - Your One Stop for Excel Tips & Solutions

Pasted URLs Don’t Become Hyperlinks

October 27, 2023 - by

Pasted URLs Don’t Become Hyperlinks

Problem: I pasted hundreds of web site addresses into Excel. They did not turn into hyperlinks. I found that I could select a cell, press F2, then Enter to make the hyperlink. But I don’t want to have to do that hundreds of times.

Five URLs, entered in A1:A5. None are "hot" - they are all just text and are not clickable.
Figure 1385. Pasted hyperlinks are not hot.

Strategy: Use the =HYPERLINK() function. Insert a blank column near your data. Use =HYPERLINK(A1,A1).

In cell B1, use =HYPERLINK(A1,A1) to create a hyperlink. The first argument is Link Location. The second argument is Friendly Name.
Figure 1386. The second A1 is supposed to be a friendly name.

Enter the formula and copy it down to all rows.

From the plain text in A, you now have live hyperlinks in B.
Figure 1387. Column B contains live hyperlinks.

Hide column A, leaving column B intact.

Gotcha: This strategy works great for web addresses that have the leading http://. It will not work for cell A5. A hyperlink will appear, but when someone follows the hyperlink, it will say the address is invalid. In that case, you could use this formula: =HYPERLINK(“http://”&A5,A5).

This article is an excerpt from Power Excel With MrExcel

Title photo by Patrick Federi on Unsplash