When you copy and paste website content from browser to Microsoft excel, the formatting of the text is retained to the extent that the unerlying hyperlinks also get pasted. If you have pasted a large portion of text, then it is a time consuming task to remove those hyperlinks from all the cells. Here is a short tutorial on creating a macro in excel to help you remove all the hyperlinks quickly.
It’s very easy, just copy and paste few lines from this post in your excel and you’re done.
Note: This excel tip will remove all the hyperlinks from the currently active worksheet only.
Step1: While the excel document is open, press [ALT]+[F11]. This combination of keys will open the visual basic editor where you will create a macro to remove the hyperlinks.
Step2: Click on “Insert” from the menu on the top, then click on the sub menu called “Module”. You would now see a window opening on the right hand side of the editor window.
Step3: In this window, copy and paste the below mentioned code.
‘Remove all hyperlinks from the currently active sheet
Step4: Now click on “File” menu item, select “Close and return to Microsoft Excel”. The visual basic editor window will close and you will return to Microsoft excel document window.
Assuming that you have the sheet, that has hyperlinks that you want to automatically remove, is open, you will run the Macro “RemoveLinks” which we created using the steps mentioned above.
To run the macro, click on Tools -> Macro -> Macros and select “RemoveLinks” from the list of Macros and click on Run button. That’s it. The macro will scan through the sheet and remove all the hyperlinks. You can also open the Macros screen by pressing [ALT]+[F8].
This macro saved a lot of my time which I would have otherwise wasted removing hyperlinks from each and every cell in the excel sheet. I hope this will save a lot of your time too going forward.
[tags]Excel Tips, MS Office Tips, Excel Macro[/tags]