Decode from URL of hyperlink

[Murali Srinivasan]

I am copying some text from the browser which are actually
hyperlinks (text is hyperlinked).

When I paste it in Excel, the text is hyperlink and it is
linking to the web page, I want to go.

My requirement is to store the text & underlying hyperlink
URL separately in 2 columns. Is there a way to get the
hyperlink property (through macro or by any function)

Thanks in advance
Murali

 

[Dave Peterson]

One way to extract those URL's from a hyperlink is with a userdefinedfunction.

Here's one that may help:

Option Explicit
Function GetURL(Rng As Range) As String
Application.Volatile

Set Rng = Rng(1)

If Rng.Hyperlinks.Count = 0 Then
GetURL = ""
Else
GetURL = Rng.Hyperlinks(1).Address
End If
End Function

So if you had a hyperlink in A1, you could put =getURL(a1) in that adjacent
cell.

Then convert those formulas to values. Then use =hyperlink(b1) instead.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

And after you convert those formulas to values (edit|copy, edit|paste
special|values), you can delete the links from the other cells.

Select the cells and run this macro:

sub deletelinks()
selection.hyperlinks.delete
end sub