How to extract URLs from hyperlinked text

[ameenakbar]

I have data like this.

Google
MSN

<tbody>
</tbody>

in above data google and msn are hyperlink with URLs.
Now I want extract URLs in next cells, like below.

Google	https://google.com (with formula)
MSN	https://msn.com

<tbody>
</tbody>

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.