Tom’s Tutorials for Excel: Delete Hyperlinks, Keep Their Text

Tom’s Tutorials for Excel: Delete Hyperlinks, Keep Their Text

At some point you have probably inherited or created a worksheet that contains active hyperlinks, when all you want in the cells is the hyperlink text itself. It’s annoying to unwittingly click onto a hyperlink cell that interrupts your work by suddenly taking you away from your worksheet.



You could right-click on each hyperlink cell and select “Remove Hyperlink”. But if you have dozens or hundreds of hyperlinks on a worksheet as depicted in the above picture, you’ll need a faster and easier method, for example by following these 3 simple steps:

Step 1
From your worksheet press Alt+F11, which takes you to the Visual Basic Editor. From there, press Ctrl+G to show the Immediate Window where your mouse cursor will be flashing.

Step 2
As you see in the picture of the Immediate Window in the VBE, type the command
ActiveSheet.Hyperlinks.Delete
and press the Enter key.



Step 3
That’s it!
Exit the VBE by pressing Alt+Q to return to your worksheet.

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,
2 comments on “Tom’s Tutorials for Excel: Delete Hyperlinks, Keep Their Text
  1. Bigger Don says:

    Tom…Interesting to me is I’ve seen this same discussion in another forum recently. That host made a similar suggestion.

    I use the method of right-clicking the upper left corner (selecting the whole sheet and bringing up the context menu) then clicking on Delete Hyperlinks. I don’t know if I’ve always done it that way and I no longer have a machine with pre-2007 Office so I cannot test if it only started working this way in 2007 and later.

    Are there times when how I do it does not work, maybe pre-2007, or conditions that block doing this?

    Thanks

  2. Ajit says:

    Very informative
    Thanks a lot sir

Leave a Reply

Your email address will not be published. Required fields are marked *

*