Results 1 to 4 of 4

Thread: Hyperlinks (XP)

  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Hyperlinks (XP)

    I have a spreadsheet that was copied from the internet. For the email address, a hyperlink appears with just the word "email". If you hover over the word "email", the full email address appears. Is there any way to get the full email address is a separate column?

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Hyperlinks (XP)

    Paste this function into a module in the workbook:
    <pre>Function GetEmailHyperlink(ByVal rngInput As Range) As String
    If rngInput.Cells.Count > 1 Then Set rngInput = rngInput.Cells(1)
    If rngInput.Hyperlinks.Count > 0 Then GetEmailHyperlink = _
    Replace$(rngInput.Hyperlinks(1).Address, "mailto:", "")
    End Function
    </pre>

    and then use <code>=GetEmailHyperlink(A1)</code> in the worksheet.
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlinks (XP)

    this didn't seem to work. I've attached a sample of what I'm trying to do. I included the module (hopefully the right way!)

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Hyperlinks (XP)

    The code needed to go in a normal module rather than a worksheet module but, more importantly, it didn't paste in properly. I have attached a revised version which should work for you! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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