Excel - removing hyperlink in cell

  VNAM75 11:46 14 Jun 2007
Locked

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
Copy
Paste Special
Values

Does that not do what you want?

Regards,

Simsy

  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
hl.Delete
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