Excel - removing hyperlink in cell

  VNAM75 11:46 14 Jun 2007

I have a range of number values and they are formatted as hyperlinks. I want to convert these to just the raw data in the same way as you would using paste special values in cells with formulas. Any help appreciated.

  Simsy 11:54 14 Jun 2007

Select the range
Paste Special

Does that not do what you want?



  keef66 11:55 14 Jun 2007

create a new, blank worksheet. Highlight the whole of your existing sheet by clicking in the very top left hand square (not the cell, the grey bit above / to the left) and copy.
Then go to your new worksheet, click in cell A1 and use edit / paste special / values

  keef66 11:56 14 Jun 2007

Simsy types quicker than me

  VNAM75 12:00 14 Jun 2007

No that doesn't work. They are actually linked to an external webpage not within excel. I've done a separate column and put in formula to bring in the hyperlink cell and then done paste special values which seems to work. Thanks.

  VNAM75 12:02 14 Jun 2007

keef66, that works. I tried to do paste special values directly on the cells which doesn't work, but if you paste on to new sheet it does!

  VoG II 12:27 14 Jun 2007

... but I was looking for a macro solution:

Sub RemoveLinks()
Dim hl As Hyperlink, s As String, r As Range
Application.ScreenUpdating = False
For Each hl In ActiveSheet.Hyperlinks
s = hl.TextToDisplay
Set r = hl.Range
r.Value = s
Next hl
Application.ScreenUpdating = True
End Sub

This works fine with text hyperlinks but if the displayed text is a number then TextToDisplay returns nothing!

I am puzzled to say the least...

  keef66 12:34 14 Jun 2007


This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Dell XPS 13 9370 (2018) review

The art of 'British' pulp fiction

Best password managers for Mac

TV & streaming : comment regarder le Tournoi des Six Nations 2018 ?