Wednesday, March 7, 2012

[EXCEL] Removing multiple hyperlinks

Removing a bunch of hyperlinks can be tedious. Imagine the time you have to spend right-clicking on each of those links and selecting ‘remove hyperlink’ on the context menu. Of course it would just take two clicks, but what if you are to remove a hundred of them or perhaps 1000 hyperlinks? I’m sure that would be enough to scrape out the paint of your right-mouse button. :)

Below are some simple techniques you can use in order to remove single to multiple hyperlinks on a page:
Removing just one hyperlink
1. Right-Click on the cell and select Remove Hyperlink from the context menu.

Removing hyperlinks using the cell format
1. Select the cells having hyperlinks. You can use CTRL+LEFT CLICK to randomly select a cell.
2. On the menu click on Edit. Hover your mouse to the option Clear and select Fomats. Please note that the cell is still clickable to remove the hyperlink completely you have to select Clear->All.

Remove Excel hyperlinks on selected cells the better way
1. Type in any text or number in a blank cell
2. Right-click and select Copy on the context menu.
3. While pressing CTRL, select each cell with the hyperlink you wish to be removed
4. On the Edit menu, select Paste Special.
5. Under Operation, click Multiply and then click OK.
6. If you are using Office 2007, you may need to click or change the cell format to Normal found at the Styles Group Toolbar.

Removing Excel hyperlinks using a macro
Assuming you know excel programming you could create a macro to automatically remove the hyperlinks.
1. Start Visual Basic Editor. Alternatively you can press ALT-F11 to start the editor.
2. Double click the workbook you are using on the Project Explorer.
3. Type the following text:

Sub RemoveHyperlinks()
ActiveSheet.Hyperlinks.Delete
End Sub

4. Save your work.
5. Run your macro by pressing ALT-F8 or using the menu by Tools->Macro->Macros
6. Select the macro you have made, it should have the name ‘RemoveHyperlinks’.

Removing the Default Hyperlink Option

The most efficient way to remove them is by actually avoiding them in the first place. Now you don’t need to follow those hard and confusing steps above anymore. You can prevent Excel from formatting URLs and network links by removing its AutoFormat function at the AutoCorrect dialog box. I am using Office 2007, see the image below:





Unfortunately, if you are using Office 2003 or lower, i can’t give you the exact steps but the AutoCorrect is at Tools on the main menu bar. I just don’t remember where to go from there. If you are using Office 2000, there is no way we could prevent URLs from being formatted.

No comments:

Post a Comment