Excel Question #46

Kevy Baby · 08-08-2008, 02:18 PM

Is there a way to tell Excel to stop turning email addresses in Excel databases into hyperlinks? We keep (small) databases in Excel and when we enter an email address, it automatically turns it into a hyperlink.

Previously, I just ignored this, but ran into an issue today where someone who I gave the database to so that they could email things for me was just clicking on the hyperlinked email address to address the email. The problem is that on a few records, when an email address was changed (usually because of personnel change for that record), the hyperlink did not automatically update.

I was able to do a mass change to ensure that the problem does not occur for the list as it exists right now, but I am searching for a long term solution.

Ghoulish Delight · 08-08-2008, 02:22 PM

It's in the AutoCorrect settings in Tools. Deselect "Internet and network paths with hyperlinks" box on the "AutoFormat As You Type" tab.

Alex · 08-08-2008, 03:17 PM

That is the way to do it, but keep in mind that is an application level setting, not document. So everybody who uses the doc will have to do that change, and once done will apply to all your documents.

Gemini Cricket · 08-08-2008, 03:44 PM

Quote:

Originally Posted by Ghoulish Delight

It's in the AutoCorrect settings in Tools. Deselect "Internet and network paths with hyperlinks" box on the "AutoFormat As You Type" tab.

Awesome. I had the same prob. It can also be done in Word as well. Yay!

Kevy Baby · 08-08-2008, 03:51 PM

Quote:

Originally Posted by Ghoulish Delight

It's in the AutoCorrect settings in Tools. Deselect "Internet and network paths with hyperlinks" box on the "AutoFormat As You Type" tab.

Quote:

Originally Posted by Alex

That is the way to do it, but keep in mind that is an application level setting, not document. So everybody who uses the doc will have to do that change, and once done will apply to all your documents.

Thank you. I looked everywhere except in Autocorrect settings. I couldn't find anything in help either.

Alex · 08-08-2008, 03:58 PM

Pretty much any and all formatting that Word/Excel do automatically can be controlled through AutoCorrect.

AutoCorrect is a feature much reviled by light users of the application (and justifiably so for some of the default settings) but is among one of the most powerful tools for advanced users. Just a mild example but currently I have reason to type this phrase a lot: HL/CL Statement Suppression Q1 2009 - Project #XXXXX. And it has to be in that form. Because of AutoCorrect and a little bit of acclimation I just type /hl and it automatically gets transformed into the correct phrase. I have another one for my full signature block. I know some people who have turned Word into a shorthand machine their AutoCorrect list is so extensive and their comfort in typing that way so practiced.

Kevy Baby · 08-08-2008, 04:07 PM

I have a few things I have "programmed" in Word to do that. I agree that it is a great tool!

Kevy Baby · 12-12-2008, 07:16 PM

Rather than starting yet another Excel help thread, I drudged up an old one.

If I have a series of cells that are calculation results, is there a way to get a count of just the cells with a result value of greater than zero (or conversely, those cells with result value of zero)

I tried using DCountA, but that didn't work (unless I wrote the formula incorrectly).

Any ideas

ETA: I found my own answer: a CountIF function

Alex · 12-12-2008, 07:20 PM

Dammit, how can I look brilliant if you go and answer your own questions?

Kevy Baby · 12-12-2008, 08:01 PM

Quote:

Originally Posted by Alex

Dammit, how can I look brilliant if you go and answer your own questions?

It took me 45 minutes of searching before I found it: how long would it have taken for you to provide the answer?

08-08-2008, 02:18 PM	#1
Kevy Baby Chowder Head Join Date: Jan 2005 Location: Yes Posts: 18,500	Excel Question #46 Is there a way to tell Excel to stop turning email addresses in Excel databases into hyperlinks? We keep (small) databases in Excel and when we enter an email address, it automatically turns it into a hyperlink. Previously, I just ignored this, but ran into an issue today where someone who I gave the database to so that they could email things for me was just clicking on the hyperlinked email address to address the email. The problem is that on a few records, when an email address was changed (usually because of personnel change for that record), the hyperlink did not automatically update. I was able to do a mass change to ensure that the problem does not occur for the list as it exists right now, but I am searching for a long term solution. __________________ The thing about quotes on the internet is that you cannot verify their validity. - Abraham Lincoln
	Submit to Quotes

08-08-2008, 02:22 PM	#2
Ghoulish Delight I Floop the Pig Join Date: Jan 2005 Location: Alternative Swankstyle Posts: 19,348	It's in the AutoCorrect settings in Tools. Deselect "Internet and network paths with hyperlinks" box on the "AutoFormat As You Type" tab. __________________ 'He who receives an idea from me, receives instruction himself without lessening mine; as he who lights his taper at mine, receives light without darkening me.' -TJ
	Submit to Quotes

08-08-2008, 03:17 PM	#3
Alex . Join Date: Feb 2005 Posts: 13,354	That is the way to do it, but keep in mind that is an application level setting, not document. So everybody who uses the doc will have to do that change, and once done will apply to all your documents.
	Submit to Quotes

08-08-2008, 03:58 PM	#6
Alex . Join Date: Feb 2005 Posts: 13,354	Pretty much any and all formatting that Word/Excel do automatically can be controlled through AutoCorrect. AutoCorrect is a feature much reviled by light users of the application (and justifiably so for some of the default settings) but is among one of the most powerful tools for advanced users. Just a mild example but currently I have reason to type this phrase a lot: HL/CL Statement Suppression Q1 2009 - Project #XXXXX. And it has to be in that form. Because of AutoCorrect and a little bit of acclimation I just type /hl and it automatically gets transformed into the correct phrase. I have another one for my full signature block. I know some people who have turned Word into a shorthand machine their AutoCorrect list is so extensive and their comfort in typing that way so practiced.
	Submit to Quotes

08-08-2008, 04:07 PM	#7
Kevy Baby Chowder Head Join Date: Jan 2005 Location: Yes Posts: 18,500	I have a few things I have "programmed" in Word to do that. I agree that it is a great tool! __________________ The thing about quotes on the internet is that you cannot verify their validity. - Abraham Lincoln
	Submit to Quotes

12-12-2008, 07:16 PM	#8
Kevy Baby Chowder Head Join Date: Jan 2005 Location: Yes Posts: 18,500	Rather than starting yet another Excel help thread, I drudged up an old one. If I have a series of cells that are calculation results, is there a way to get a count of just the cells with a result value of greater than zero (or conversely, those cells with result value of zero) I tried using DCountA, but that didn't work (unless I wrote the formula incorrectly). Any ideas ETA: I found my own answer: a CountIF function __________________ The thing about quotes on the internet is that you cannot verify their validity. - Abraham Lincoln
	Submit to Quotes

12-12-2008, 07:20 PM	#9
Alex . Join Date: Feb 2005 Posts: 13,354	Dammit, how can I look brilliant if you go and answer your own questions?
	Submit to Quotes