How to easily remove hyperlinks from multiple cells?

Question

I have data exported from a tool as XLS. In this, one of the column has text which is a hyperlink to information in the tool. I can remove the hyperlink for each cell. But I want to remove the hyperlink for all the cells in the column in a easier way. Is there an option in Excel itself?

Answer 1

An option would be to let a VBA Macro do the donkey work.

Press Alt-F11, double-click This Workbook, then copy this code into the resulting window (essentially only one line of code!):

Sub RemoveHyper()
    Selection.Hyperlinks.Delete
End Sub

Press Alt-F11 to go back to your spreadsheet, select all the cells with hyperlinks, then do Alt, T, M, M and select the RemoveHyper Macro, then select Run.

When you're done, if you want to remove the Macro, either save the file as .xlsx, or press Alt-F11 and remove the code.

Answer 2

Follow these steps:

1. In any empty cell, type in the number 1 (it can be deleted later).
2. Right click the cell where you typed the number 1 and then click on Copy.
3. Select the cells with hyperlinks that you want to delete.
4. On the Home tab, in the Clipboard group, click the down arrow and then click on Paste Special.
5. Under Operation, click on Multiply, and then click OK.
6. On the Home tab, in the Styles group, click on Cell Styles. From the drop down menu, click on Normal.
7. Now the hyperlinks are deleted. You can now delete the number 1 used to perform this task.

Answer 3

I highlighted the range that had multiple hyperlinks embedded. I copied them to another section of the spreadsheet, then, used the Special Paste function, and pasted them as values into the new section. Worked like a charm!

Answer 4

Select all cells you want to clear from hyperlinks, then click the "Home" tab in the ribbon at the top of Excel. In the "Editing" group, click on the "Clear" pull-down arrow and select "Clear Formats".

UPDATE:

Select all cells you want to clear from hyperlinks, right click and "Cut" or use Ctrl+X. Now open up a notepad window and paste it there. Now select all the lines with content from notepad and right click "Cut" or use Ctrl+X. Now select the top cell of the just cut column in Excel and right click "Paste" or use Ctrl+V.

Answer 5

• Highlight all the hyperlinks you wish to remove
• Under the Home tab, go to Styles and select Cell Styles
• Click on Normal
• If you wish to add hyperlinks again, do the same thing – only select Hyperlink instead of Normal

Answer 6

Use format painter. Just select a cell that isn't a hyper link (or make one with a right click "remove Hyperlink" then select the whole column and click on the format panter button again.