Removing hyperlinks (2003)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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. 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?

Reply | Quote

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 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(“A1:D10”).Hyperlinks.Delete

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

That’s OK. Thanks for the explanation.

Reply | Quote