How to Remove Hyperlinks From an Excel Document

Recently a friend of ours approached us with a large document that for some reason he needed to work on, the unfortunately part of it was that he has simply copied the contents of the official website and dumped it in his excel workbook.

So you can imagine how many hyperlinks he had to deal with in the data cleaning process(over 300 rows). There are many ways of skinning a cat so they say and we had the our favorite methods, we had three options, method one was the most popular and tiresome(right click select remove hyperlink), method two was the inglorious copy and multiply by one, however our favorite method was the scary macro.

Method 1

1. Select all the cells containing hyperlinks you want to remove.
2. Right click the cells and then click on Remove Hyperlinks option, all the hyperlinks in the selected cells will be deleted. 

As stated the first option is tiresome though its very popular, it works well with a limited number of data that is if you are not manipulating many worksheets or rows of data.

this method may not work or office 2007  and you have to repeat the process for every worksheet that has hyperlinks.

Method 2

1. Type 1 in any empty and then press Ctrl + C
2. Select the cells that have hyperlinks you want to remove
3. Right click any selected cells, click "Paste Special" then click the "Multiply" radio button
4. Click OK to have all the hyperlinks are removed from the worksheet

Just like method 1, method 2 requires a lot of selecting and works very well with a limited amount of data, and surprisingly it works well with all versions of excel

Method 3

1. Click Macros and then click create
2. Copy and paste this in the console press F5

           Sub RemoveAllHyperlinks()

                 ActiveSheet.Hyperlinks.Delete 

          End Sub

All hyperlinks will disappear from  the current worksheet


These are the methods we use, what about you, which methods would you pick?