Converting a Range of URLs to Hyperlinks

@AJ
Re. my most recent comment below: On third thought, simply replace the following line
            ActiveSheet.Hyperlinks.Add Anchor:=cell, Address:=sURL
with these lines
            On Error Resume Next
                ActiveSheet.Hyperlinks.Add Anchor:=cell, Address:=sURL
            On Error GoTo 0
This should work in either the Tip's version or in my first version of the macro. It skips over cell values that do not make valid http hyperlinks.

@AJ
Re. my previous comment below: On second thought, replace the following line
            ActiveSheet.Hyperlinks.Add Anchor:=cell, Address:=sURL
with these lines
            Dim n As Integer
            n = InStr(Mid(sURL, InStr(sURL, "://") + 4), "/")
            If n = 0 Then n = Len(sURL)
            If InStr(Left(sURL, n), " ") + InStr(Left(sURL, n), "%") = 0 Then
                ActiveSheet.Hyperlinks.Add Anchor:=cell, Address:=sURL
            End If
because the address part of a URL must not contain any space characters and the Hyperlinks.Add method will not accept %20 as a substitute. Spaces in the subaddress part are automatically replaced by %20. For example, consider the following cell values:
http://my site.com -- cannot create hyperlink
http://my%20site.com -- cannot create hyperlink
http://mysite.com/a b c/ -- hyperlink is http://mysite.com/a%20b%20c/

@AJ
You probably had leading space characters in some cells. Try this version:

Sub URL_List()
    Dim sTmp As String, sURL As String, cell As Range
    'WARNING: This macro assumes web site (http) URLs
    For Each cell In Selection
        sURL = Trim(cell.Value)
        If sURL <> "" Then
            sTmp = Left(sURL, 7)
            If sTmp <> "http://" And sTmp <> "https:/" Then
                sURL = "http://" & sURL
            End If
            ActiveSheet.Hyperlinks.Add Anchor:=cell, Address:=sURL
        End If
    Next cell
End Sub

Hi

For me, running this code on a selected range, causes a debug (run-time error '1004': Application-defined or object-defined error) at this stage:

ActiveSheet.Hyperlinks.Add Anchor:=cell, _
Address:=sURL, TextToDisplay:=cell.Value

Regards,

AJ

My Excel Toolbox includes the following function which is a superior substitute for Excel's HYPERLINK function:
    =SuperLink(Link_Location, [Friendly_Name], [Screen_Tip])
SuperLink is fully described in the PDF document UseSuperLink.pdf.
See https://sites.google.com/view/MyExcelToolbox/

@Bill Multack
Assuming you are referring to Ctrl+K hyperlinks, not HYPERLINK(...) formulas, select the column of hyperlinks and run this macro:

Sub ShowHyperlinkAddress()
    Dim HL As Hyperlink
    For Each HL In Selection.Hyperlinks
        HL.TextToDisplay = HL.Address
    Next HL
End Sub

I have just the opposite problem. I have a column of hyperlinks that all have display values. I would like to strip away the TextToDisplay values and only show the hyperlink. Any suggestions?