How to get rid of hyperlinks?

[Jennifer Murphy]

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 couldn't see an option that would get plain texgt
without also losing the formulas.

Thanks a bunch

 

[Jennifer Murphy]

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
=========================================

That seemed to remove them from the entire workbook. How do I get it
to remove them just from the selected cells?

 

[Rick Rothstein]

That seemed to remove them from the entire workbook.

How do I get it to remove them just from the selected cells?

See if this does what you want...

Sub RemHL()
Selection.Hyperlinks.Delete
End Sub

Rick Rothstein (MVP - Excel)

 

[Ron Rosenfeld]

That seemed to remove them from the entire workbook. How do I get it
to remove them just from the selected cells?

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]

See if this does what you want...

Sub RemHL()
Selection.Hyperlinks.Delete
End Sub

Rick Rothstein (MVP - Excel)

That did it. Thanks.

 

[Jennifer Murphy]

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.

My mistake. It only did it to the active worksheet.

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.

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]

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)

 

[Ron Rosenfeld]

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?

That is not a trivial task, but here is one work around:

http://www.contextures.com/xlToolbar02.html

 

[Ron Rosenfeld]

Maybe Ron went to bed?

I do sleep occasionally. But I woke up

Functions with arguments do not show in the macro list.
You should be able to find it in the "Insert Function" list (fx).

I think she is asking about a macro that are located in an add-in. I've 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.