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.
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
Very informative
Thanks a lot sir