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 (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.

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

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 4

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 5

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:

1. Click on the Office button at the top left corner of your excel sheet (an icon showing some boxes).
2. Down there are 2 tabs - Excel options and Excel exit. Click on Excel option.
3. Go to Proofing
4. Click Auto correct option
5. Select tab Auto Format as you type
6. Uncheck Internet and network paths with hyperlinks apply as you work.
7. Click OK button below

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.

Answer 7

• 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 8

Copy cells, paste special, "values" in another column (or row), then copy and paste back over the hyperlinked cells.

Answer 9

Per Excel 2007 help file:

To turn off several hyperlinks at once, do the following:

1. In a blank cell, type the number 1.
2. Right-click the cell, and then click Copy on the shortcut menu.
3. 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.
4. 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.)
5. Under Operation, click Multiply, and then click OK. (Top area "Paste" = All. Bottom area "Operation" = Multiply)
6. On the Home tab, in the Styles group, click Cell Styles.
7. 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.

Answer 10

Tried multiple options stated on this page with no avail, but then I got lucky:

1. Create a new worksheet
2. Select cells with the hyperlinks
3. Go to new worksheet
4. Paste Special - select All using Source Theme

This pastes the content, removes the hyperlinks and retains the original formatting.

Answer 11

To remove hyperlinks from multiple cells:

1. Highlight all the cells you want to remove the hyperlink from
2. Click on the "Home" Tab
3. Click on "Clear" in the Editing Group
4. Choose Clear Hyperlinks

Answer 12

Highlight all the cells together, right click and add a hyper link, entering any letters, such as 'xxxx'. Press OK without changing the Highlighted area, then click on remove hyperlink.

Answer 13

Select the area, then Ctrl-C the area, then Ctrl-V the same area. Voila, no more hyperlink. Then change the blue to black by selecting color black while the are is still selected.

Answer 14

just put a link in other column and then copy this link to one another column by paste special values... then this new column can be copied to that multiple cells hyper link column... SIMPLE AND EASY

Answer 15

Sub Macro2()
    Range("A1:Z300").Select
    Selection.Hyperlinks.Delete
End Sub

Running this macro eliminated all hyperlinks in the range of cells a1 thru z300

Answer 16

Step 1:Click 1 in empty cell. Step 2:And Copy that cell. Step 3:Choose the hyperlink Column. Step 4:Click the Cell style in toolbar at home. Step 5:Click normal. step 6:delete 1.