convert table of hyperlinks

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a large table (over 600 entries) of hyperlinks.
They are all in a single column.
They are all of the form: =HYPERLINK("http://www.something.com","something")
I need to convert the table into a table of hyperlinks without function
calls. The result table would look like it was made by using
Insert=>Hyperlink...

I have tried various copy/paste specials and they all failed.

I am not much of a programmer, but I can copy and paste into the VBA editor.
Thank you in advance for any guidance you give me.
 
Jake, this may not look to efficient but it will do the job that you
described.

Sub convrt_hyper()
Dim oldHyp As Hyperlink
Dim newHyp As String
Dim HypAdr As String
Dim Hyptxt As String
Dim ln As Long 'string length
Dim cnt As Long 'comma position
Dim rng As Range
Dim Lrow As Long

'in next line change A to the column letter
'where your old hyperlink functions are
Lrow = Cells(Rows.Count, "A").End(xlUp).Row

'change both A's to match column letter in line above
'change the 1 to the rownumber they begin on
Set rng = Range("A1:A" & Lrow)

'the following will put the new hyperlink
' in the next column to the right
For Each oldHyp In rng.Hyperlinks
HypAdr = oldHyp.Address
cnt = InStr(1, HypAdr, ",", vbTextCompare)
ln = Len(HypAdr)
newHyp = Left(HypAdr, cnt - 2)
Hyptxt = Mid(HypAdr, cnt + 2, ln - cnt - 2)
ActiveSheet.Hyperlinks.Add Anchor:=rng.Offset(0, 1), Address:= _
newHyp, TextToDisplay:=Hyptxt
Next oldHyp
End Sub

If this doesn't quite do it for you, we can tweak it to work.
Mike F
 
Back
Top