How to work with Hyperlinks

[ilyaz]

I have several columns with several dozens of hyperlinked entries in each
column. I'd like to unlink them all. I highlighted all entries in a column
and removed underlines, changed their color to black, but they still were
linked. Sure, I can unlink each one, but it will take several hours to
finish my job. Is there a way to unlink all hyperlinks at once, or, at
least, in each column separately?
I'll appreciate your help.

 

[SteveW]

Right-click and select hyperlink... and remove the link
Now using Format Painter copy this format to any other cell required.

Steve

 

[Epinn]

Hi,

I found the following instructions from "help." I did a test and it seemed
to work like a charm.

Deactivate several hyperlinks at once
1.. Type the number 1 in a blank cell, and right-click the cell.
2.. Click Copy on the shortcut menu.
3.. While pressing CTRL, select each hyperlink you want to deactivate.
How?

a.. To select a cell that has a hyperlink in it without jumping to the
hyperlink destination, click the cell and hold the mouse button until the
cursor becomes a cross , then release the mouse button.
4.. Click Paste Special on the Edit menu.
5.. Under Operation, click Multiply and then click OK.
Epinn

 

[Epinn]

Steve,

I followed your instructions. The format painter removed the underline but
when I clicked on the cell, the link was still there. Am I missing
something?

Epinn

Right-click and select hyperlink... and remove the link
Now using Format Painter copy this format to any other cell required.

Steve

 

[SteveW]

Sorry, I normally really check my posts in excel.
It removed the formatting, so *wrongly* I assumed it had removed the
hyperlink

Ta, for pointing it out.

Just select the range/column and right-click choose Hyperlink.. and then
*Remove Link..* from the dialogue

Steve

 

[Epinn]

Select the range/column?? Have you tested this?

"Remove hyperlink" seems to work with one cell at a time only. What
dialogue? Clicking hyperlink = insert. Did I miss anything?

So far, only copy 1 and paste special (multiply) appears to remove
**multiple** hyperlinks.

Epinn

Sorry, I normally really check my posts in excel.
It removed the formatting, so *wrongly* I assumed it had removed the
hyperlink

Ta, for pointing it out.

Just select the range/column and right-click choose Hyperlink.. and then
*Remove Link..* from the dialogue

Steve

 

[SteveW]

You don't get me twice - I double checked this time

Right-click on one gives a remove option
but right-click on a range gives ... a dialogue box
in the bottom left ..

HOLD IT - it's gone!

Actually it is there if all the selected links are the same which is what
I had in my test

Going back to bed - ta

Steve

 

[Epinn]

Steve,

For the record, I am not trying to "get you." Like you, I am a "fan" of
shortcuts. If your method is faster and works, I like to use it.

I can't get your method work. Also, I have tried many times as well.

(1) I have inserted hyperlinks to a few cells filled with numbers/text.
The hyperlinks are **different**. Have you tested with different links or
only the same links?

(2) I use same hyperlinks for the cells. I select the range and click
hyperlink and I get the dialogue box for insert hyperlink. The confusing
part is sometimes I see "remove link..." on the right (not left) bottom of
the dialogue box and sometimes I don't. I don't know why it is not
triggered ALL THE TIME. For the odd times that I see it, I click on it to
try to remove the links. Afterwards, I click on the cells and the links are
still there.

(3) If this is one method, I want to know why the help feature doesn't
mention it.

Steve, please understand that I am a detailed person and I like to get to
the bottom of things. If it is a quick method that works, I like to know.
The more we chat, the more I discover.

Yes, I was surprised that you got up so early. Hope you slept well.

Cheers,

Epinn


You don't get me twice - I double checked this time

Right-click on one gives a remove option
but right-click on a range gives ... a dialogue box
in the bottom left ..

HOLD IT - it's gone!

Actually it is there if all the selected links are the same which is what
I had in my test

Going back to bed - ta

Steve

 

[ilyaz]

Steve and Epinn,
I'm glad I woke up late today and got to the end of your discussion ).
Sure, the last suggestion from Excel's help works. But still, it requires
clicking on EACH hyperlinked cell. I have too many of them...
Thanks, guys, for making my task a little bit easier. But still, if you will
find how to remove hyperlinks in a whole column or in several columns I'll
appreciate it.
Ilya

 

[Gord Dibben]

To remove multiple hyperlinks I suggest you use David McRitchie's macro.

Sub DelHyperlinks()
Selection.Hyperlinks.Delete
End Sub

This will not rmove those created through the function =HYPERLINK(...) formula.

To get rid of those, copy an empty cell and Paste Special>Add to the range of
Hyperlinks.


Gord Dibben MS Excel MVP

 

[ilyaz]

Thanks, Gord, it works!
Ilya

To remove multiple hyperlinks I suggest you use David McRitchie's macro.

Sub DelHyperlinks()
Selection.Hyperlinks.Delete
End Sub

This will not rmove those created through the function =HYPERLINK(...)
formula.

To get rid of those, copy an empty cell and Paste Special>Add to the range
of
Hyperlinks.


Gord Dibben MS Excel MVP

 

[SteveW]

Don't worry, your comments have been valid.

In my last post, yes I did test but all were the same
I just copied one.

The *removelink* appears if they are all the same

CHecked help and every thing they only talk about one link when
editing/deleting etc

Steve

 

[SteveW]

Epinn's solution does work for a block
mine only does if *all* the hyperlinks are the same - not the usual case

Steve