How to extract URLs from hyperlinked text

[ameenakbar]

I have data like this.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Google[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MSN[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

in above data google and msn are hyperlink with URLs.
Now I want extract URLs in next cells, like below.
[TABLE="width: 200"]
<tbody>[TR]
[TD]Google[/TD]
[TD]https://google.com (with formula)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MSN[/TD]
[TD]https://msn.com[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Is there any formula / macro or some other way.

 

[admiral100]

Hi,

Try this

Sub ExtractHyper()
Dim HP As Hyperlink
For Each HP In ActiveSheet.Hyperlinks
HP.Range.Offset(0, 1).Value = HP.Address
Next
End Sub

 

[Rick Rothstein]

Here is another macro that you can try which targets a specified range (as written, cell A1 on downward)...

[table="width: 500"]
[tr]
	[td]Sub ExtractHyperlinkAddresses()
  Dim Cell As Range
  For Each Cell In Range("[B][COLOR="#FF0000"]A1[/COLOR][/B]", Cells(Rows.Count, "[B][COLOR="#FF0000"]A[/COLOR][/B]").End(xlUp))
    Cell.Offset(, 1).Value = Cell.Hyperlinks.Item(1).Address
  Next
End Sub[/td]
[/tr]
[/table]

 

[ameenakbar]

Thanks alot.

 

[ameenakbar]

Thanks you sir.