Removing multiple hyperlinks in Excel

2006 Oct 11

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


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:

autocorrect1.jpg

autocorrect2.jpg








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.


Tweet this post

Related Entries

19 Comments

Thanks!! worked great and simple to do.

Thanks!! it worked. This would be great help for the people downloading spreadsheets form internet for various projects purpose.

life saver.

Thankyou, well explained & easy to understand.

Excellent thanks was wondering how to do this for an entire worksheet.

Thanks for this, the macro was so simple.

So easy when you know how! Thank you very much for this.

I appreciate the tip on deleting multiple hyperlinks, it worked great for what I wanted to do with my spreadsheet. Thanks!

Are you kidding me? There are big problems with each method you describe. Excel is trying to be too smart. Why are hyperlinks the default and why can't we turn them off??? I have a page full of hyperlinks and I want them gone. If I clear the format, I loose my formatting. If I click on each individually, I'll spend all day at it. I don't have time to make macros. And if I did, I will need to run it on each computer I use. Where is the option to disable hyperlinking. It should be a one-click solution. This is BS.

Microsoft is retarded!!! Why is it so difficult to remove hyperlinks. Jerks.

Thanks! The Paste Special/Multiply trick works to clean links well. Except for that it leaves zeros in blank cells. That's minor compared to manually removing the links.

good one man thanks you soo much love u

Wonderful! Thanks so much for this, it saved me so much time :o)

Doesn't work if your cells contain numbers. Worked on text hyperlinks but not hyperlinks in the form of a number. It would multiply but leave the hyperlink.

Thanks, it really worked ! It was such an annoyance having to deal with formatting. There should be a simpler way to remove all hyperlinks without loosing the format of the cell.

Or you just copy the data into notepad and then paste it back into excel...

Thank you very much for the valuable tips, I learnt much & saved much time.

Yass,

I appreciate the tip on deleting multiple hyperlinks, it worked great for what I wanted to do with my spreadsheet. Thanks!

Awesome! It worked perfectly. Thank you sooooo much. You saved me a lot of time and frustration!

Leave a comment


About Me


Alfredo Sanchez is an internet professional focusing on the study search engines behavior in particular. Supports Free Open Source Software and currently develops applications with it using XAMPP.

Recent Entries

Close