extracting Hyperlink into text in cell

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!

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.
--
Gary's Student


"[email protected]" wrote:

> 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!
>
>

Hello,

Thank you, This works great!

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!

"Gary''s Student" wrote:

> 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.
> --
> Gary's Student
>
>
> "[email protected]" wrote:
>
> > 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!
> >
> >

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!

"Gary''s Student" wrote:

> 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.
> --
> Gary's Student
>
>
> "[email protected]" wrote:
>
> > 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!
> >
> >

Don't feel bad, instead read:


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



--
Gary''s Student


"FrozenRope" wrote:

> 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!
>
> "Gary''s Student" wrote:
>
> > 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.
> > --
> > Gary's Student
> >
> >
> > "[email protected]" wrote:
> >
> > > 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!
> > >
> > >