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?
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 (or Alt-F8) 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.
Follow these steps:
- In any empty cell, type in the number
1(it can be deleted later).
- Right click the cell where you typed the number
1and then click on
- Select the cells with hyperlinks that you want to delete.
- On the
Hometab, in the
Clipboardgroup, click the down arrow and then click on
Operation, click on
Multiply, and then click
- On the
Hometab, in the
Stylesgroup, click on
Cell Styles. From the drop down menu, click on
- Now the hyperlinks are deleted. You can now delete the number
1used to perform this task.
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".
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.
It is irritating when you type an email ID in an excel cell and a hyperlink automatically comes in. To get rid of getting hyperlinks further in any cell , here's the solution:
- Click on the Office button at the top left corner of your excel sheet (an icon showing some boxes).
- Down there are 2 tabs - Excel options and Excel exit. Click on Excel option.
- Go to Proofing
- Click Auto correct option
- Select tab
Auto Format as you type
Internet and network paths with hyperlinks apply as you work.
- Click OK button below
Per Excel 2007 help file:
To turn off several hyperlinks at once, do the following:
- In a blank cell, type the number 1.
- Right-click the cell, and then click Copy on the shortcut menu.
- Hold down CTRL and select each hyperlink that you want to turn off. -- Tip -- To select a cell that has a hyperlink in it without going to the hyperlink destination, click the cell and hold the mouse button until the pointer becomes a cross, then release the mouse button.
- On the Home tab, in the Clipboard group, click the arrow below Paste, and then click Paste Special. (note: can right click, select paste special.)
- Under Operation, click Multiply, and then click OK. (Top area "Paste" = All. Bottom area "Operation" = Multiply)
- On the Home tab, in the Styles group, click Cell Styles.
- Under Good, Bad, and Neutral, select Normal. (Top left if "Cell Styles" is expanded. White box, black text, yellowish outline around box.)
It worked for me.