Remove all hyperlinks in Microsoft Word or Excel

22.06.2011

guest

In Tips & Advice

This article will help you find out how to use two excellent Macros to remove all the hyperlinks from Microsoft Word and Excel documents.

Firstly, what is a Macro?

A macro is a set of computer instructions that you can record and associate with a shortcut key combination or a macro name. Then, when you press the shortcut key combination or click the macro name, your computer program carries out the instructions of the macro. This saves you time by replacing an often-used, sometimes lengthy series of actions with a shorter action. For example, instead of clicking several menus and buttons to add text to your business documents in Microsoft Word, you can record those steps in a macro and then just click the macro to add the text in one step.

Office lets you automate not only keystrokes, but also most of the actions you can perform in Office. Almost anything you can do with the Office menu choices, with the toolbar buttons, and in the document editing environment can be automated with macros.

Now to the exciting part: Have you ever tried copying and pasting something from the Internet into Word or Excel only to find that the hyperlinks have been carried over? Now you can remove them easily with the Macros below.

Word:

Step 1

Hit [ALT]+[F11] to open the Visual Basic Editor

Step 2

Go to “Insert” > “Module” and paste the following into the pop-up window:

Sub RemoveHyperlinks()

Dim oField As Field

For Each oField In ActiveDocument.Fields

If oField.Type = wdFieldHyperlink Then

oField.Unlink

End If

Next

Set oField = Nothing

End Sub

Step 3

Then click “File” > Close and return to Microsoft Word

Step 4

You can now run the Macro in Word by going to:

View > Macros > View Macros and then Run “RemoveAllHyperlinks”

If you want to retain this Macro in the saved document, just save it as a Word Macro-Enabled Document (*.docm), otherwise save it as a Word Document (*.docx)

Excel:

You can do the same in an Excel Document:

Step 1

Hit [ALT]+[F11] to open the Visual Basic Editor

Step 2

Go to “Insert” > “Module” and in the pop-up window copy;

Sub RemoveHyperlinks()

'Remove all hyperlinks from the active sheet

ActiveSheet.Hyperlinks.Delete

End Sub

Step 3

Then click “File” > Close and return to Microsoft Excel

Step 4

You can now run the Macro in Excel by going to:

View > Macros > View Macros and then Run “RemoveAllHyperlinks”, this will delete all URLS on the selected worksheet.

If you want to retain this Macro in the saved document, just save it as a Excel Macro-Enabled Workbook (*.xlsm), otherwise save it as a Excel Workbook (*.xlsx)

Image: photostock / FreeDigitalPhotos.net