Jerry Leventer

Are you an Excel user? Have you ever wondered how to convert an entire column of plain text links into hyperlinks without having to double click each one individually?

Don’t look in the Excel Help files for this one, it’s not even mentioned there. The following code is exactly what you need.

Convert Selected Plain Text Links To HyperLinks

Public Sub Convert_To_Hyperlinks()
    Dim Cell As Range
    For Each Cell In Intersect(Selection, ActiveSheet.UsedRange)
        If Cell <> "" Then
            ActiveSheet.Hyperlinks.Add Cell, Cell.Value
        End If
    Next
End Sub

Convert Selected HyperLinks To Plain Text

Public Sub Convert_From_Hyperlinks()
    Intersect(Selection, ActiveSheet.UsedRange).Hyperlinks.Delete
End Sub

Instructions On How To Create The Macro

Step 1: From within your Excel workbook, open the macro editor by pressing ALT+F11.

Step 2: In the Tools Menu, left-click View and select Project Explorer.
View -> Project Explorer (CTRL+R)

Step 3: Right-click the Project you are working on.

Step 4: Right-click ‘Modules’, select ‘Insert’, then select ‘Module’.
Modules -> Insert -> Module

Step 5: Paste the code into the project module you have selected.

Step 6: Press ALT+F11 to return to your Excel workbook (or click on its icon in the Windows taskbar).

Run The Macro

Step 1: To execute the macro you just created, select the plain text links you want to convert to hyperlinks.

Step 2: Press ALT+F8 to open the Macro selector window and click on the macro you just created.

Note: ALT+F11 means you press AND HOLD the ALT key at the same time you press the F11 key.

[tags]Microsoft Excel Tutorial, Visual Basic, Hyperlink, Macros[/tags]