extracting Hyperlink into text in cell

[awilhite]

Hello,

I have a pretty simple need to take a hypertext link and copy the URL
as text in the next field over. The links have a name "click here for
details" that cover up the url and I am having problems extracting it.
The urls are pulled from a web query. I would like to be able to
this through a macro or a function call.

In addition, I need to pull out data in the middle of the get string
and put in the adjacent cell as well.

Can anyone recommend how best to accoplsih this or point me in in a
certain direction?



Thank you!

 

[Guest]

FIrst enter this tiny UDF:


Function hyp2(r As Range) As String
hyp2 = r.Hyperlinks(1).Address
End Function

If A1 contains a hyperlink, =hyp2(A1) will return the URL.

 

[awilhite]

Hello,

Thank you, This works great!

 

[Guest]

I feel like a dork, but I'm not familiar with how to create a UDF or how to
work with personal.xls. Is there a primer that you can direct me to that can
help?

I have a 2,000 row spreadsheet where I need to extract the URL an embedded
column, and can't stand the thought of doing that by hand. ;o)

Thanks in advance for baby-sitting me on this!

 

[Guest]

I feel like a dork, but I'm not familiar with how to create a UDF or how to
work with personal.xls. Is there a primer that you can direct me to that can
help?

I have a 2,000 row spreadsheet where I need to extract the URL an embedded
column, and can't stand the thought of doing that by hand. ;o)

Thanks in advance for baby-sitting me on this!

 

[Guest]

Don't feel bad, instead read:


http://www.mvps.org/dmcritchie/excel/getstarted.htm