Results 1 to 3 of 3

How to remove "text to display" for hyperlink. in excel 2007

This is a discussion on How to remove "text to display" for hyperlink. in excel 2007 within the Excel Questions forums, part of the Question Forums category; Remove "text To Display" For Hyperlinks hi I have a list of Hyperlinks in one column which have always a ...

  1. #1
    New Member
    Join Date
    Mar 2010
    Posts
    2

    Default How to remove "text to display" for hyperlink. in excel 2007

    Remove "text To Display" For Hyperlinks

    hi
    I have a list of Hyperlinks in one column which have always a display name like:
    i am using excel 2007

    Email

    Name


    Behind each Email or Homepage a Hyperlink is hidden. But for every Email address in my file the "text to display" is Email.

    Can you help me to find a macro which deletes all display names and shows me the hyperlink like this:

    Peter@xyz.de
    www.google.de
    Carl.True@hotmail.com
    www.msn.de

    etc.

    Thanks a lot
    Best regards
    Rossi

  2. #2
    New Member
    Join Date
    Mar 2010
    Posts
    2

    Smile Re: How to remove "text to display" for hyperlink. in excel 2007

    i found this very helpful hope this helps. this is for Excel 2007

    u need to first select the hyperlink cells and then press "Alt+F11"

    u'll get a new window to setup Macro
    just choose the sheet the hyperklink cells u have selected and then paste this

    Sub HyperLinkAddressToRight()
    Dim h As Hyperlink

    For Each h In Selection.Hyperlinks
    h.Range.Offset(0, 1).Value = h.Address
    Next h
    End Sub

    then go back to the excel sheet where u have selected the hyperlinked cell u want to extract and press "Alt+F8" and click Run.
    hope this works

    Cheers

  3. #3
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    11,532

    Default Re: How to remove "text to display" for hyperlink. in excel 2007

    Code:
    Sub Strip_Hyperlink_Names()
        Dim HLink As Hyperlink
    
        For Each HLink In ActiveSheet.Hyperlinks
            HLink.TextToDisplay = Replace(HLink.Address, "mailto:", "")
        Next HLink
    
    End Sub

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com