This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
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.
Figure 1235 Pasted hyperlinks are not hot.
Strategy: Use the =HYPERLINK() function. Insert a blank column near your data. Use =HYPERLINK(A1,A1).
Figure 1236 The second A1 is supposed to be a friendly name.
Enter the formula and copy it down to all rows.
Figure 1237 Column B contains live hyperlinks.
Copy the column of formulas and use Paste Values to paste back on top of the formulas. This will get rid of the hyperlink formula and leave you with only the hyperlinks. You can now copy those hyperlinks back over the original data.
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).