Results 1 to 9 of 9
  1. #1
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    220
    Thanks
    13
    Thanked 0 Times in 0 Posts

    Removing hyperlinks (2003)

    I have a field that's formatted as text and contains URLs. I don't want them to appear as hyperlinks, but only as text. When I imported the data from a CSV file, the URLs did appear as text. However, if I edit an URL in a cell, the URL turns into a hyperlink as soon as I finish. I can right-click and remove the URL, but that's tiresome. I didn't find it in the Help, but can I turn off or remove hypelinks from a given worksheet, permanently? Thanks.
    JimmyW
    Helena, MT

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Removing hyperlinks (2003)

    To prevent URLs from being turned into hyperlinks when you edit a cell:
    - Select Tools | AutoCorrect Options...
    - Activate the AutoFormat As You Type tab.
    - Clear the check box "Internet and network paths with hyperlinks".
    - Click OK.

    To turn existing hyperlinks into ordinary text values:
    - Activate the Visual Basic Editor (Alt+F11)
    - Activate the Immediate window (Ctrl+G)
    - Type ActiveSheet.Hyperlinks.Delete and press Enter.
    - Switch back to Excel.

  3. #3
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    220
    Thanks
    13
    Thanked 0 Times in 0 Posts

    Re: Removing hyperlinks (2003)

    Thanks, Hans! I turned off the hyperlink option in Tools as you suggested.
    JimmyW
    Helena, MT

  4. #4
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing hyperlinks (2003)

    Edited by HansV to provide link to post. It's extremely easy: just include the brackets [ and ] and the Lounge software will create a link.

    Hans, I have a question about this. I have a SS with several columns; col A has hyperlinks but shows text (that is, you see the company name but must click on it to follow the link). Col B has the URL only which I extracted using a function you described in <post:=537,320>post 537,320</post:>. This URL is an active link and is blue and underlined.

    Now, I did what you suggested here and it did, indeed, remove the hyperlinks from Col A but it left Col B intact (blue and underlined). My question is why? I am pleased that this happened but why didn't it change all the links to text?

    Moreover, if I did want to change the actual URL links to mere URL text, how would I do that? Finally, if I want to select part of a column, or row, or a rectangle, would would I apply the same immediate procedure to 'unlink' the selected cells?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Removing hyperlinks (2003)

    The GetAddress function itself doesn't produce hyperlinks, it just returns a text string. So if column B contains clickable hyperlinks, you must somehow have turned these cells into hyperlinks.
    But the method from <post:=710,109>post 710,109</post:> in this thread should remove ALL hyperlinks, so I don't understand what's happening.

    If you want to remove hyperlinks from selected cells only, use

    Selection.Hyperlinks.Delete

    instead of

    ActiveSheet.Hyperlinks.Delete

    You can also specify an explicit range:

    Range("A110").Hyperlinks.Delete

  6. #6
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing hyperlinks (2003)

    Thanks for the info. In fact, I did use the getaddress function just as you explained it. I put the function in column B and extracted the address from Col A (replicated down Col . The result was for Col A to lose the hyperlink and become plain text while Col B became the hyperlink showing the ful URL.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Removing hyperlinks (2003)

    Placing a formula in column B should have no effect on column A, i.e. it should not cause column A to lose its hyperlinks. Neither should the cells in column B act as hyperlinks. So as I mentioned before, I don't understand what's going on.

  8. #8
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing hyperlinks (2003)

    What is going on is simple. I was wrong.

    Here is what happened... When I inserted a new column (Col , it inherited the formatting of Col A in which each cell was blue and underlined (as they were hyperlinks). Then I used the getaddress function in Col B and replicated it down the column; it worked perfectly in copying the URLs from Col A into Col B... of course, each URL inherited the formatting from Col A (blue & underlined). I assumed that since they looked like hyperlinks that they were, in fact, hyperlinks; but they were not hyperlinks, the cells contained only text with hyperlink formatting. Then when I removed the hyperlinks using the immediate procedure, it worked and removed all the hyperlinks from Col A (also removing the hyperlink formatting from Col A). However, that procedure did nothing with Col B since Col B merely had the appearance of hyperlinks (text that was blue and underlined) but there were no actual hyperlinks.

    In short, I have wasted your time. Sorry for that.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Removing hyperlinks (2003)

    That's OK. Thanks for the explanation.

Posting Permissions

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