+ Reply to Thread
Results 1 to 6 of 6

extracting Hyperlink into text in cell

  1. #1
    awilhite@cableone.net
    Guest

    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!


  2. #2
    Gary''s Student
    Guest

    RE: extracting Hyperlink into text in cell

    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


    "awilhite@cableone.net" 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!
    >
    >


  3. #3
    awilhite@cableone.net
    Guest

    Re: extracting Hyperlink into text in cell

    Hello,

    Thank you, This works great!


  4. #4
    FrozenRope
    Guest

    RE: extracting Hyperlink into text in cell

    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
    >
    >
    > "awilhite@cableone.net" 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!
    > >
    > >


  5. #5
    FrozenRope
    Guest

    RE: extracting Hyperlink into text in cell

    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
    >
    >
    > "awilhite@cableone.net" 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!
    > >
    > >


  6. #6
    Gary''s Student
    Guest

    RE: extracting Hyperlink into text in cell

    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
    > >
    > >
    > > "awilhite@cableone.net" 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!
    > > >
    > > >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1