Cant unformat email address hyperlink in Excel the way HELP says I can

[tomseeley]

I'm about to put my foot through my computer! I hate it when I go to a help screen, I understand the message I find, and the program won't behave the way the help screen says it will!

I'm running Excel 2010 which I'm comfortable with without being an expert. I've inherited a membership list file that has a column containing members' email addresses. Some of the members' email addresses in that column are formatted as a hyperlink format, so that they automatically launch my email program when I click on those entries. Some of the addresses in the same column are in a format that LOOKS like the typical hyperlink format (blue underlined etc) but which are actually simply text format. When I click on those addresses in that column, I do not launch my email program. That's how I want all the entries in that column to behave!

I do NOT want ANY of them to automatically launch my email program!!!!!!!!!!!!

I went to the Office online help and found a message for how to delete a hyperlink. It says I click and hold the cell containing the entry I do NOT want to be a hyperlink that will automatically launch my email, and the cursor will change from the pointer to a cross. OK. Been there done that. It then says Right click and in the context-sensitive drop down menu that appears, click "remove hyperlink". I can understand that, too.

But guess what! When I do that, the "remove hyperlink" entry in the drop down menu is grayed out!

What am I doing wrong?

How am I supposed to do what I want to when the program doesn't behave the way the help message says it does!

 

[Kari]

Whatever I try, I cannot reproduce your issue. Removing "mailto" hyperlinks works exactly as it should work both on Excel 2010 and 2013. Only way to not be able to select Remove Hyperlink in context menu is if the cell is protected, editing disabled, in which case the context menu does not even have the Remove Hyperlink item as this screenshot shows:

​

By default Excel adds a "mailto" hyperlink to each email address:

​

You do not need to Click and hold the cell, simply select it and right click, select Remove Hyperlink:

​

The email address no longer has a hyperlink:

​

Kari

 

[tomseeley]

Kari,

I wish I could duplicate YOUR environment! I'm not trying to argue or pick a fight, but I'll stand by what I said. When I do exactly what you said to do, the remove hyperlink is grayed out! I guess I'll have to live with it, even though it means I can't ever click on any of these offending cells or it will launch my email program in the middle of what else I'm trying to do without being interrupted by something I don't want!

Thx for trying.

 

[Kari]

Please test this:

Type an email address in an empty cell on a new workbook created just for this test, hit Enter. Select the cell, right click. Is the Remove Hyperlink item on context menu still grayed out?

 

[Callender]

Suggest - Try ASAP Utilities add on

Hi,

Sorry to butt in but if you can't get Excel working the way it should be working there's a pretty good add in available for free with tons of utilities.

It can:

Remove all hyperlinks

I use it on my machine at work mainly for text manipulation and deduplication of records but it has so many other features too.
Hierarchical list of all ASAP Utilities (sub)menu items


This add on only works with MS Excel 32bit versions which is typically installed even on 64bit machines. Basically if you see MS Office listed in your Program Files x86 folder then you've got 32bit MS Office installed.
Download ASAP Utilities

 

[tomseeley]

Kari,

First of all, let me say thank you for continuing to stay involved in this puzzle. For me, it's a PITA but it's not a showstopper! I'm curious but I'm not going to be obsessed about it much longer (I hope; knowing me, I might, though!).

Here's what I can report:

1. I copied a link from cell G7 in the orig file, which appears with a "mailto..." message as illustrated above, so I know it's a hyperlink in the orig file. I pasted this single sell into cell A1 in a new, blank workbook. At this point, it is still a hyperlink.

2. In the new workbook, I right click on cell A1 and the "remove hyperlink" choice is available.

3. I click "remove hyperlink" and cell A1 reverts to a normal cell. It is no longer a hyperlink. The "mailto.." thing is no longer present.

4. I then copy this modified not-a-hyperlink cell A1 back into cell G7 in the orig file. Cell G7 is still a hyperlink! The remove hyperlink choice is no longer available when I right click on cell G7!


Arrrggghhhhhhhhhhhhhh

Like I said, I can work around it, so feel free to ignore me from this point forward, unless it's got you puzzled enough too that you want to keep working on it!

Thanks again.

Tom

 

[Kari]

Check the Autoformat as you type option, if automatic links is enabled, disable it, close and reopen Excel and see if the Remove Hyperlinks is now visible.

​

Kari

 

[tomseeley]

No the remove hyperlink choice is still grayed out.

 

[Kari]

I have no more ideas. I'll keep looking, posting here if I stumble upon something.

 

[Slartybart]

I haven't used Excel in a while, please excuse the rust.

Try this:
Save the spreadsheet as a .csv file (tab delimited)
>> If there is more than one sheet in the Excel file, just try one.
>> if this works, you can process any other sheets in the Excel file

Open the .csv file as a new Excel speadsheet (only tab delimited in the import dialog)
>> You should be able to double click the .csv file and it will open an import dialog in Excel

Are the eMail addresses still mailto:'s
Can you remove hyperlink from them in this version of the Excel file?

 

[Ranger4]

Perhaps you could try this. Copy all those with the hyperlink active into a new workbook & change them to a normal cell. Then instead of copying them back to the previous workbook, copy the ones in the previous workbook to the new workbook.
Sounds stupid but it might work.

 

[tomseeley]

Kari,

Hey thanks man! No problem! I really appreciate your help! But I think the issue has run its course, for at least 3 reasons: 1) It's not a big problem for me; it's only a curiosity, that I don't have to solve; and 2) I'm headed for Palm Springs, CA, for two weeks vacation! It's 19 deg here in SC right now, so this is something I'm really looking forward to! And I don't intend to take Excel issues along with me! and 3) I just tried what someone on another help forum suggested, and presto! I "copy/paste special-ed" and it works!

 

[tomseeley]

Hey SlartyBart, Thx for helping! I just posted a reply above, saying someone else suggested another way to copy/paste, which seems to work. I appreciate your help too.

By the way. Your screen name sounds vaguely familiar. Any chance it comes from the Hitchhiker's Guide...?

 

[Slartybart]

Does "I designed the fjords, all those squiggly lines"
or
All the flower pot could think of was "Oh no, not again!"
or
Come along or you'll be late!
Late for what?
Late as in the Late Arthur Dent.

answer your question re: my forum name?

Gee Tom, I feel kind of bad for you, having to go to Palm Springs, maybe I'll head sown to SC for a warm up.



Before you go, How did you originally put the data in Excel? and Is this something you do with any regularity?
You might want to poke around in Excel to see if there is a way to prevent future issues with hyperlinks.

Anyway, enjoy your 2 weeks in Sunny CA.

Bill

 

[bawldiggle]

Excel 2003 had a habit of occasional corrupt files

As a newbie to Excell 2010 while rewriting an addin, my tiny experimental .xlsm file corrupted.
Yep ... Excel can do it sometimes.

 

[whs]

Did you first highlight the hyperling before you tried to remove it ?

 

[Slartybart]

Kari,

Hey thanks man! No problem! I really appreciate your help!
But I think the issue has run its course, for at least 3 reasons:

1. It's not a big problem for me; it's only a curiosity, that I don't have to solve; and
2. I'm headed for Palm Springs, CA, for two weeks vacation! It's 19 deg here in SC right now, so this is something I'm really looking forward to! And I don't intend to take Excel issues along with me! and
3. I just tried what someone on another help forum suggested, and presto! I "copy/paste special-ed" and it works!

Thread Start is probably in Palm Springs by now. A workaround resolved the issue.

 

[jdat]

I got it!

@TomSeeley - I was having exactly the same problem! Fixed it by: Un-sharing the workbook.

Let me know if this works for you, too!