Removing Or Avoiding Automatic Hyperlinks

Category: Formatting / VBA Functions | [Item URL]

You may have discovered that Excel 2000 (and later versions) supports automatic cell hyperlinks. Whenever you type something that resembles a URL or an e-mail address into a cell, this feature automatically converts the text into a clickable hyperlink. But what if you don't want to create a hyperlink?

If you use Excel 2000, you're out of luck. There is no way to turn this potentially annoying feature off. But you can, however, override it. If Excel creates a hyperlink from your cell entry, click the Undo button (or press Ctrl-Z) to restore the cell's contents to normal text. Or, you can precede the cell entry with an apostrophe.

Note: If you're using Excel 2002 or later, you can turn automatic hyperlinks on or off in the AutoCorrect dialog box

Surprisingly, Excel doesn't provide a direct way to remove all hyperlinks on a worksheet. In fact, the only way to accomplish this is one cell at a time: Activate the cell that contains the hyperlink, right-click, and then select Hyperlink, Remove Hyperlink. Unfortunately, this command is available only when you have selected a single cell. To quickly deactivate all hyperlinks, you need to use a macro.

To create the macro, press Alt-F11 to activate the Visual Basic Editor, select Insert, Module to insert a new VBA module into your project, and then enter the following code:

Sub ZapHyperlinks() 
    Cells.Hyperlinks.Delete
End Sub

When you execute the ZapHyperlinks macro, all hyperlinks on the active sheet are deleted and replaced with normal text.