In Excel, I pasted in a bunch of URLs, but they aren't live links — how do I bulk convert them to live links?

Question

In Excel, I pasted in a bunch of URL's into a column.

However, they aren't live links after pasting.

How do I bulk convert them all to live links swiftly?

Answer 1

This should do the trick.

Sub Test()
  Dim rngRange As Range
  For Each rngRange In Selection
    If Trim(rngRange.Value) <> "" Then
      rngRange.Hyperlinks.Add _
               Anchor:=Cells(rngRange.Row, rngRange.Column), _
               Address:=rngRange.Text, _
               ScreenTip:=rngRange.Text, _
               TextToDisplay:=rngRange.Text
      End If
  Next rngRange
End Sub

If you have to do it a lot, I'd hook it up to a keyboard shortcut.

To make this happen in Excel 2010 (compliments of @Excellll)

1. Go to "View" and then click the "Macros" button.
2. This will open a dialog box asking for the name of a new macro. Enter "Test" and click "Create".
3. This will open the VBA editor. Copy the code above into this new window.
4. Now go to your spreadsheet and select the range of cells with the hyperlinks you want to fix.
5. Go back to the VBA editor. Press F5. This will execute the code.