Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

14 Answers 14

up vote 16 down vote accepted

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.

share|improve this answer
    
This one also worked like a charm. More technical way of solving the problem. Thank you. –  TheMaskOfZero Sep 24 '10 at 15:12
    
+1 Much more elegant than mine! –  BloodPhilia Sep 28 '10 at 20:09

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.
share|improve this answer
1  
+1 This is how I learned a long time ago and works everytime within Excel. –  Hondalex Sep 24 '10 at 15:38

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

Screen Shot

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.

share|improve this answer
    
Thanks for the answer; But that doesn't clear the hyperlinks. Am I doing something wrong? –  TheMaskOfZero Sep 24 '10 at 11:11
    
@TheMaskofZero That is odd, it seems to work just fine for me... Are you selecting the right cells? Some text that doesn't fit in the cell makes an overlap with neighbouring cells thus making it seem as if that were the cells they are in. –  BloodPhilia Sep 24 '10 at 11:22
    
@BloodPhilia I am selecting exactly those cells which have the hyperlink. I am having all cells in the same column and they are continuous. I tried this by selecting a single cell. Even then the hyperlink is not cleared. –  TheMaskOfZero Sep 24 '10 at 11:49
    
@TheMaskofZero That is odd... I'll add a workaround to my answer in a minute. –  BloodPhilia Sep 24 '10 at 11:56
    
@BloodPhilia Thank you! The workaround worked. –  TheMaskOfZero Sep 24 '10 at 12:08

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!

share|improve this answer

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
share|improve this answer

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.

share|improve this answer
2  
This one did not work. –  TheMaskOfZero Sep 26 '11 at 6:46
  • 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
share|improve this answer
    
Doing this just changes the look of text. The cells still have the hyperlink. Clicking on cell again takes me to the tool. –  TheMaskOfZero Mar 19 '12 at 9:41

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

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

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
share|improve this answer

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.

share|improve this answer
    
Great if it worked for you, but I do not think this is a universal solution. –  pnuts Jan 15 '13 at 1:38

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.

share|improve this answer
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

share|improve this answer
    
-1 Basically the same as accepted answer 2 years earlier –  nixda May 19 at 17:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.