Removing Links

[Guest]

Is there a way to remove all links from a spreadsheet, or to disable the ability to follow links? I copied a rather large table from the web to reformat so I can print it for quick reference, but as I'm formatting I often accidentally click on words in the cells, which are all links. This then opens IE, and causes me all sorts of headaches.

Any suggestions

 

[Jason Morin]

Here is a macro from MVP David McRitchie (I removed one
line of code) that disables the hyperlinks and converts
them to normal text. Press ALT+F11, Insert > Module, paste
this code in. Then return to XL, select the range of
cells, and run the macro.

Sub MakeTextOnlyFromHyperlinks()
'David McRitchie, 2000-08-23 worksheet.functions !!
Dim cell As Range
Dim URL As String
For Each cell In Selection
If IsEmpty(cell) Then GoTo chknext
URL = ""
On Error Resume Next
URL = cell.Hyperlinks(1).Address
If Err.Number = 9 Then GoTo chknext
If Trim(URL) = "" Then GoTo chknext
cell.Value = URL
cell.Hyperlinks(1).Delete
chknext: On Error GoTo 0
Next cell
End Sub

--
HTH
Jason
Atlanta, GA

-----Original Message-----
Is there a way to remove all links from a spreadsheet, or

to disable the ability to follow links? I copied a rather
large table from the web to reformat so I can print it for
quick reference, but as I'm formatting I often
accidentally click on words in the cells, which are all
links. This then opens IE, and causes me all sorts of
headaches.

 

[Jason Morin]

You could use a formula that extracts the word between the
2 periods:

=MID(A1,FIND(".",A1)+1,FIND(".",A1,FIND(".",A1)+1)-FIND
(".",A1)-1)

HTH
Jason
Atlanta, GA

-----Original Message-----
Hmmm...
Any modifications to that code that would keep the

original text in the field as opposed to the actual
hyperlink?

i.e. The field with the word Dog goes to

http://www.dog.com and when I run the code I end up with
http://www.dog.com instead of just Dog.