Remove all hyperlinks within Excel
Ever copy a webpage of data into Excel, and it brings along a bunch of hyperlinks? Of course, it isn't practical to remove each hyperlink manually. Fortunately, there are a couple of options to perform a bulk removal of hyperlinks.
Note that I'm using Excel 2007 in my examples.
- Select the cells that contain hyperlinks.
- On the Home tab, in the Editing section, click the Clear drop-down arrow.
- Click Clear Hyperlinks.
Use the following macro:
Sub NoMoreHyperlinks()For those of us that need a reminder on how to create macros, here's a more detailed process.
- Press Alt-F8 to display the Macro dialog box
- Enter a new macro name (such as "NoMoreHyperlinks") in the text box just below "Macro name:"
- This will change the Create button from an inactive to active state
- If you already have one or more macro, the name of one may already be in the "Macro name:" text box. That's ok, just type over it and Excel will realize you're wanting to create a new macro.
- Click Create -- this opens the Visual Basic Editor
- Copy the lines above and paste into the edit window
- Note if you didn't use "NoMoreHyperlinks" as a name, you will need to make sure you update the macro code accordingly
- Close the editor window -- you should be back on your worksheet
- Press Alt-F8 again
- Select your new macro and click Run -- all your objects should now be deleted from your worksheet