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!

[BobBridges]

My bet is that the option is grayed out, disabled, for some reason that does not have to do with the help topic; the poor sap who wrote that wasn't thinking about the possibility that the Remove-Hyperlink might be disabled because of .... because of .... well, because of some option that I can't think of just now.

By the way, I just tested it and you don't have to click-and-hold first to select the cell; you can just right-click straight on the cell, even if it's not the current selection, and it'll bring up the menu. And on my machine you don't have to do this one cell at a time; you can highlight a bunch of cells with hyperlinks in them, right click on the whole selected range and find Remove Hyperlink in the resulting menu. It works, too ... on my machine. But if it doesn't work for one cell on your machine, it probably won't work for many, either. The trick here is to figure out why you're not allowed to Remove Hyperlink just now. Is it an Options setting? Are you in the middle of running a macro? Are you in <F2> mode (whatever that's really supposed to be called)?

I googled "Excel Remove Hyperlink disabled" and found this. Does that fit your case?

[tomseeley]

Bob, I'll bet you're right. Another helper suggested the file could be protected in some way, which is something I did not think of.

I did find a workaround. I copied the offending column to an entirely new workbook. That by itself carried the offending hyperlinks (for SOME of the entries, but not all of them, even though they all looked like hyperlinks; I still haven't figured that part out) into the new workbook. But then, when I selected the entire column the "remove hyperlinks" choice did appear and it did work. So I then recopied the corrected column back over the original column in the original workbook and I'm good to go.

Thanks for your thoughts!

[tomseeley]

Well dang, it didn't work after all. Back to square one

[BobBridges]

Wait, what didn't work? You mean that after you copied the email addresses back to the original worksheet, they turned back into hyperlinks? (You gotta be specific, Tom.)

If that's what you meant, then maybe I should have explained that you can turn off Excel's eagerness to create hyperlinks in the Options. Let's see, here... Ah, here it is: File, Options, Proofing, AutoCorrect Options, go to the AutoFormat As You Type tab, uncheck "Internet and network paths with hyperlinks".

And remember, that link I pointed out said that the workbook might be protected or shared, if that's any help.

[tomseeley]

Originally Posted by BobBridges

Wait, what didn't work? You mean that after you copied the email addresses back to the original worksheet, they turned back into hyperlinks? (You gotta be specific, Tom.)

Yeah that's exactly what I meant! Sorry I wasn't more specific! In fact, it's not quite that simple. As near as I can tell, once I copied the email addresses back into the original, SOME of them turned back into hyperlinks but not all of them! That's bizarre!

If that's what you meant, then maybe I should have explained that you can turn off Excel's eagerness to create hyperlinks in the Options. Let's see, here... Ah, here it is: File, Options, Proofing, AutoCorrect Options, go to the AutoFormat As You Type tab, uncheck "Internet and network paths with hyperlinks".

Thanks for this suggestion. I know how to check this and I will. I think this might be a solution. I knew they made addresses hyperlinks by default but I had not gotten around myself to checking to see if I could turn that off! I will.

And remember, that link I pointed out said that the workbook might be protected or shared, if that's any help.

I got this suggestion from another helper also. I actually really thought that was the problem since (and again I failed to mention this in the OP) the file I inherited IS a shared file! I did check the properties and the protection attribute, but it is entirely unprotected. I have not checked how it gets made a "shared" file yet, so I don't know if that's the problem. But from what I know about the source of the file I am betting you're onto something here!

I really appreciate your help! This is a PITA to me, but frankly it's not a showstopper. I'll make it clear when you don't need to keep working on this for me, if you haven't already reached that point!

Thanks very much.

[tomseeley]

..... I should have explained that you can turn off Excel's eagerness to create hyperlinks in the Options. Let's see, here... Ah, here it is: File, Options, Proofing, AutoCorrect Options, go to the AutoFormat As You Type tab, uncheck "Internet and network paths with hyperlinks".


Bob, I tried this and I think it would prevent any new email addresses I enter into the spreadsheet from being autoformatted as hyperlinks, but I've got nearly 700 lines of data, and I don't want to have to retype every email address to get them NOT to be hyperlinks! After I did this, the remove hyperlink choice is still not available.

I tried looking at protecting the file. It's completely unprotected. It is a shared file, however, but I don't know what that does or how to undo that.

Anyway...I'm done for the night.

Thanks again for helping!

Ain't technology grand?!

[BobBridges]

Gee, I was thinking—maybe I'm still thinking—that now that you have automatic hyperlinks turned off, you can do what you did before: copy that whole column of email address to another workbook, "break" the hyperlinks (if you still need to) and then copy them back. With that option turned off, I was thinking you'd get plain text even in the original shared workbook.

Of course, it may be easier to make it "not shared", remove the hyperlinks and then share it again. Problem is, I don't really know what's meant by "shared". Is it in a Sharepoint directory? Probably not that. Is there a flag somewhere? Does it simply mean that some other user has it open at the same time? I'm not sure.

I don't mind answering more questions, but I have no more ideas to offer just now until you hand me some.

[excelledsoftware]

Personally I have always had luck with removing any hyperlinks by selecting all of the cells with hyperlinks in them. Copy the range. Then paste special. Select multiply and paste it. This has always removed all hyperlinks for me and it's very quick to do.

[tomseeley]

Bill

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 the next message suggested, and presto! I "copy/paste special-ed" and it works!




Thx again.

[tomseeley]

Thanks, Excelledsoftware! Your idea worked perfectly! Problem solved.

[excelledsoftware]

Originally Posted by tomseeley

Thanks, Excelledsoftware! Your idea worked perfectly! Problem solved.

You are very welcome