Excel - removing hyperlink in cell

  VNAM75 11:46 14 Jun 07
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 07

Select the range
Copy
Paste Special
Values

Does that not do what you want?

Regards,

Simsy

  keef66 11:55 14 Jun 07

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 07

Simsy types quicker than me

  VNAM75 12:00 14 Jun 07

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 07

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 07

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

??

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

Sniper Elite 4 review: Headshotting Nazis has never felt so good

1995-2015: How technology has changed the world in 20 years

The Fresh New Fonts of 2017

WWDC 2017 dates: How to get WWDC 2017 tickets, when is WWDC 2017 and more details announced