Macro To Create Hyperlink from html text

[Guest]

In excel cell, there are html hyperlink content like <a href="www.mysite.com"
alt="My Link">My Link</a>.

Now using this cell content, the excel hyperlink has to be created. Could
anyone help me to create a macro? Also in the excel worksheet, there will be
around 150k cells having such cells which needs to be converted in hyperlink.

 

[David McRitchie]

Hi Ketan,

<a href="www.mysite.com"> alt="My Link">My Link</a>.

convert to:
=HYPERLINK("www.mysite.com", "My Link">

Seems like something that could be easily done with
Regular Expressions, but that is not my thing. The following
assumes the links are as stated with or with ALT=
but without extra spaces and probably machine generated.

There may have been easier ways to get the information, I don't
know how you created an HTML link format within a cell, and
possibly could have generated the HYPERLINK Worksheet Function
more directly..

Sub convert_href()
Dim i As Long, j As Long, rng As Range, cell As Range
Dim s1 As String, s2 As String
'-- <a href="www.mysite.com" alt="My Link">My Link</a>
On Error Resume Next
Set rng = Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
If rng Is Nothing Then Exit Sub
On Error GoTo 0
For Each cell In rng
If LCase(Left(cell, 9)) = "<a href=""" Then
i = InStr(10, cell, """")
If i > 0 Then
s1 = Mid(cell, 10, i - 10)
j = InStr(cell, ">")
If j <> 0 Then
s2 = Mid(cell, j + 1, Len(cell) - j - 4)
MsgBox "=HYPERLINK(""" & s1 & """,""" & s2 & """)"
cell.Formula = "=HYPERLINK(""" & s1 & """,""" & s2 & """)"
End If
End If
End If
Next cell
End Sub