batch conversion of URL text to hyperlink

[spacia]

I have about 2000 urls in a spreadsheet that are recorded as text. If
I copy the text to the hyperlink address field the text in the cell
becomes an clickable URL. How do I do this for all 2000 in one wave
of the magic wand?

Thanks, Chris

 

[Dave Peterson]

I would use a formula in an adjacent column:

Something like:
=hyperlink(a1,"Click Me")
or
=hyperlink("http://" & a1, "Click Me")

 

[spacia]

I would use a formula in an adjacent column:

Something like:
=hyperlink(a1,"Click Me")
or
=hyperlink("http://" & a1, "Click Me")

Thanks Dave. This does work to put a clickable link in the
spreadsheet but there must be a way to actually convert that text to a
clickable URL by copying the sting in the cell to the Hyperling
address field. I just don't know how.

-CB

 

[Gord Dibben]

Sub MakeHyperlinks()
'David McRitchie
Dim Cell As Range
For Each Cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
With Worksheets(1)
.Hyperlinks.Add anchor:=Cell, _
Address:=Cell.Value, _
ScreenTip:=Cell.Value, _
TextToDisplay:=Cell.Value
End With
Next Cell
End Sub


Gord Dibben MS Excel MVP