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

Best phone camera 2017

Stunning new film posters by Hattie Stewart, Joe Cruz & more

iPad Pro 10.5in (2017) review

28 astuces pour profiter au mieux de votre iPhone