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