Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
    
Perhaps you can record a macro on one of them and play it back for all of them. –  user3463 Jun 23 '11 at 22:46

1 Answer 1

up vote 3 down vote accepted

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.
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.