Excel - How to get rid of hyperlinks?

Asked By Jennifer Murphy on 08-Mar-11 11:39 AM
I pasted a large table from a website into a worksheet. Only after I
did a lot of work formatting and editing, did I realize that many of
the cells contain hyperlinks.

Is there a way to convert all of the data in that sheet into plain
text without affecting the text itself or any of the many formulas I
have set up (sums and percentages)?

I tried copying the data to another sheet and using the Paste Options
dropdown, but I could not see an option that would get plain texgt
without also losing the formulas.

Thanks a bunch




Ron Rosenfeld replied to Jennifer Murphy on 08-Mar-11 01:21 PM
You can use a Macro.

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub),  navigate to the sheet you wish to convert.  (I'd do this on a copy, first, just in case).

Then <alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.

==========================================
Sub RemHL()
Cells.Hyperlinks.Delete
End Sub
=========================================
Jennifer Murphy replied to Ron Rosenfeld on 08-Mar-11 06:02 PM
That seemed to remove them from the entire workbook. How do I get it
to remove them just from the selected cells?
Rick Rothstein replied to Jennifer Murphy on 08-Mar-11 06:27 PM
See if this does what you want...

Sub RemHL()
Selection.Hyperlinks.Delete
End Sub

Rick Rothstein (MVP - Excel)
Ron Rosenfeld replied to Jennifer Murphy on 08-Mar-11 07:04 PM
The code will remove all the links from the active worksheet.   Do you mean to write that it is removing hyperlinks from other sheets also?  That's strange.

If you only want to remove hyperlink from selected cells, change the 2nd line to:

Selection.Hyperlinks.Delete

and, before you run the macro, select the cells you wish to have the hyperlinks removed from.
Jennifer Murphy replied to Rick Rothstein on 08-Mar-11 09:19 PM
That did it. Thanks.
Jennifer Murphy replied to Ron Rosenfeld on 08-Mar-11 09:23 PM
My mistake. It only did it to the active worksheet.


Thanks, works perfectly.

One question: I put the macro in my personal add-in containing a bunch
of math functions. I am able to access any function in that add-in
from a cell (=myfun(a1)). But when I tridd to run this macro (RemHL)
from Alt-F8, it did not show up. However, if I post the name in the
name field, then the Run button leaps to life and the macro works
perfectly.

What do I have to do to get it to be listed?
Jim Cone replied to Jennifer Murphy on 08-Mar-11 11:06 PM
Maybe Ron went to bed?
Functions with arguments do not show in the macro list.
You should be able to find it in the "Insert Function" list (fx).
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(Extras for Excel add-in:  convenience built-in)
(includes: hyperlinks removal and upper/lower/sentence case)




-snip-
Ron Rosenfeld replied to Jennifer Murphy on 09-Mar-11 12:48 AM
That is not a trivial task, but here is one work around:

http://www.contextures.com/xlToolbar02.html
Ron Rosenfeld replied to Jim Cone on 09-Mar-11 12:51 AM
I do sleep occasionally.  But I woke up :-)


I think she is asking about a macro that are located in an add-in.  I have not seen any of my macros that are stored in my add-in, show up in the Run Macro dialog box.  I came across this:  http://www.contextures.com/xlToolbar02.html on Debra Dagleish's site, that might be useful.