14

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?

16 Answers 16

20

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.

4
  • 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! – Pylsa Sep 28 '10 at 20:09
  • 1
    If your on OSX just go to the top menu bar -> tools -> macros -> visual basic editor, then type the code into this workbook.. then go back to the macros menu and run it after selecting all cells with links. – Abram Mar 31 '16 at 14:05
  • +1 on osx, file format xlsx will throw out macros. you need to save in old xls format – μολὼν.λαβέ Sep 11 '17 at 1:00
9

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.
4
  • 1
    +1 This is how I learned a long time ago and works everytime within Excel. – Hondalex Sep 24 '10 at 15:38
  • It added 0's to all my empty cells. Not a problem in this case but noteworthy to avoid in future. – rom016 Mar 6 '16 at 11:20
  • @rom016: Since an empty cell has a value of 0, multiplication by 1 results in 0. This is no problem for the above case as the relevant cells are not empty. – Mehper C. Palavuzlar Mar 10 '16 at 14:03
  • @Mehper C. Palavuzlarbut other cells could be highlighted to save time picking each one out. – rom016 Mar 10 '16 at 14:12
6

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.

7
  • 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. – Pylsa 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. – Pylsa Sep 24 '10 at 11:56
  • @BloodPhilia Thank you! The workaround worked. – TheMaskOfZero Sep 24 '10 at 12:08
5

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!

2

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
1
  • This is about preventing cell contents to be converted to hyperlinks, please explain how it solves the current problem (converting hyperlinks back to plain text) – Máté Juhász Oct 6 '15 at 7:46
1

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.

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

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

1

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.

0

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.

0

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
0

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.

0
0

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.

-1
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

1
  • -1 Basically the same as accepted answer 2 years earlier – nixda May 19 '14 at 17:07
-1

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

1
  • 1
    Is this really about removing the links? – Wrzlprmft Dec 4 '15 at 12:20
-2

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.

1
  • 2
    This is a broken copy of this answer. Please only answer a question if you have anything to add to the existing answers. – Wrzlprmft Aug 3 '16 at 11:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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