ExcelBanter - Macro To Create Hyperlink from html text

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
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Ketan Patel" wrote in message ...
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.

--
Ketan Patel
Apima Consulting Pvt. Ltd.