Exported HyperLink text to actual Hyperlink

[SpillerBD]

I have a third party website that I can export data from. In that data are text values that represent Hyperlinks, but Excel does not immediately recognize them as Hyperlinks. Editing the cell without changing the text allows Excel to see that it is a hyperlink, but that technique is obviously too laborious for 100's of cells.
Other than using the function HYPERLINK():

• Does anyone know of a simple method of converting to Hyperlinks?
• Does anyone have/written a simple macro that would convert text in selected cells to clickable Hyperlinks?

 

[Scott Huish]

Sub Test()
Dim c As Range, strLink As String
For Each c In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
If c.Hyperlinks.Count = 0 Then
    strLink = c
    If Left(strLink, 4) <> "http" Then c = "http://" & c: c.Hyperlinks.Add c, strLink
End If
Next
End Sub

 

[SpillerBD]

Close.
The text I receive includes "https" at the beginning of each.
To avoid changing the range in the Macro, I changed to selected range method too. I use the SHIFT+CTRL+ "cursor direction" habitually. Also, the source reporting is flexible enough that depending on the request I satisfy, the links may not always be in the same column.
My Final Code is:

Sub MHTTPS()
Dim c As Range, strLink As String
Dim SRange As Range
Set SRange = Selection
For Each c In SRange
If c.Hyperlinks.Count = 0 Then
    strLink = c
    If Left(strLink, 4) = "http" Then c = c: c.Hyperlinks.Add c, strLink
End If
Next
End Sub

Since my case includes the "http" only in the cells needed to be changed, I might change to .CurrentRegion

 

[Scott Huish]

You could eliminate the SRange variable, since it is only used once. You also don't need strLink any longer because you're not changing the value.

Sub MHTTPS()
Dim c As Range
For Each c In Selection
If c.Hyperlinks.Count = 0 Then
    If Left(c, 4) = "http" Then c = c: c.Hyperlinks.Add c, c
End If
Next
End Sub