Publication Date: January 27, 2010
This newsletter is being sent to 14,000+ Excel 'addicts' around the world
using Aweber, the best solution for managing your email campaigns.
Hi fellow Excel Addict,
Through this newsletter, I receive emails from many of my readers who are just as passionate about Excel as I am and you're probably one of them. I also know that the vast majority of Excel users aren't even remotely aware of what they have at their fingertips. Please share your passion for Excel with others.
Only 4 days left in my Excel Addicts Supporting Haiti campaign. Click the image on the right to find out how much has been raised so far. Thank you for your support.
I hope you like this weeks tips.
Keep on Excelling,
Francis J Hayes (The Excel Addict)
Please type 'The Excel Addict' in the Subject field of your emails.
If you reply to this message, please delete the newsletter content
below before sending. To remove the background color in
Outlook 2007, click Options, Page Color, No Color.
This week's tips..
1) Count How Many Times A Specific String Of Text Occurs In A Range (XL2000-XL2007)
2) Customize Your 'Open' and 'Save As' Dialog Boxes (XL2003-XL2007)
3) How To Really Delete A File
If you're having trouble displaying graphics in this email, I have posted an online HTML version of this week's newsletter here.
Last week's tips were...
1) How To Calculate Elapsed Time Greater Than 24 Hours (XL2000-XL2007)
2) Restrict User's Access To A Specific Worksheet Range (XL2000-XL2007)
3) Easily Move Paragraphs In Microsoft Word
You can still find last week's newsletter here.
You can access even more tips on my website by going to my members' page.
Make sure you save a copy of your spreadsheet before trying these tips, just in
case you make an error or the procedure doesn't produce the results you want.
Count How Many Times A Specific String Of Text Occurs In A Range (XL2000-XL2007)
I was recently asked, "What formula can I use to count how many times a specific string of text appears in a certain range (or the whole sheet)?"
There is a difference between 'how many cells contain that string of text' and 'how many times that string of text occurs in a range'. For example, if a cell contains the specific string of text twice, it should be counted as two.
I've seen all kinds of formulas thrown around suggesting ways of doing this.
One formula is... =COUNTIF(range,"text"). However, this counts only cells whose entire contents matched the text.
Another formula is... =COUNTIF(range,"*text*") uses asterisks on both ends of the text string to find the text anywhere within the cell. The problem with this formula is that, if a cell contains two instances of the text string, it gets counted only once.
Here is a formula that WILL count the number of occurrences of a text string in a range. Note that this is an 'array formula' and therefore you need to press CTRL+SHIFT+ENTER to complete the formula. When you do, you will notice curly braces surrounding the formula. You cannot edit these curly braces, you must press CTRL+SHIFT+ENTER each time you edit the formula.
Or you can use this very simple 'Non-Formula' trick...
If you simply want to count the number of times a certain string of text occurs in a range or sheet, including multiple occurrences in a single cell, here's a simple way to do it without having to remember which formula to use.
Select a range of cells if you want to limit the search to a specific range or select a single cell if you want to search the whole worksheet. Press CTRL+H to open the Find & Replace dialog. Type your search string in the Find field and type the exact same text in the Replace field. Click Replace All and a message will pop up telling you how many occurrences of the text were replaced.
Now isn't that easier than any of those formulas?
Note that this will work sometimes by using Find All and seeing a message displayed on the bottom of the Find dialog, however, this will not count multiple occurrences in cell. Notice that the message on the Find dialog counts 'cells' not occurrences.
Customize Your 'Open' and 'Save As' Dialog Boxes (XL2003-XL2007)
In Excel 2003 and 2007, you've probably noticed a navigation bar on the left-hand side of the Open and Save As dialog boxes with options such as Recent, My Documents, Desktop. This makes it easy to navigate to some frequently-used places for opening or saving files without having to click your way through a maze of folders.
Something that is not obvious about this navigation bar is that you can customize it with folders that YOU frequently open and save to.
1) Open the Save As or Open dialog as you normally do;
2) Browse to and select a folder you want to add to the 'My Places' bar;
3) In Excel 2007, right click anywhere in the My Place bar and click Add 'folder_name'. In Excel 2003, click the Tools dropdown on the top right corner of the dialog and select 'Add to My Places';
Your folder is now displayed on the My Places Bar. The next time you need to Open a file from or Save a file to this folder, just click on the folder icon to instantly jump to the folder.
If you want to reposition your folder on the bar, right-click on it and use the Move Up or Move Down options. You can also reposition any of the other icons on the bar. You can add up to 256 icons to the My Places bar. If you add more icons that can display in the My Places bar, a scroll button will appear at the bottom. Notice also that there is a Remove option on the right-click menu.
It's also worth noting that you can customized the My Places bar in both Word and Excel and any folders added to it will appear in the My Places bar for both programs.
If you're not 100% satisfied, simply request a full refund from ClickBank.com
How To Really Delete A File
Most people know that when you delete a file in Windows it goes into the Recycle Bin and stays there until you empty it. But there may be times when you want to really delete the file so that someone else with access to your computer cannot just go to the Recycle Bin, restore the file and view it. Maybe its a sensitive file that you want gone.
You could right-click on the Recycle Bin icon and empty it, but there is a simple way to have a file deleted without having it go into the recycle bin. Simply hold down the Shift key when you delete a file.
A WORD OF CAUTION: When you delete a file in this manner, IT IS GONE. If you accidentally delete the wrong file IT IS GONE. Make sure, when you delete a file in this way, that you are SURE you have the right file. Believe me, I learned this the hard way.
Spreadsheet Tips From An Excel Addict is available only to subscribers of my newsletter.
If this newsletter was forwarded to you and you would like to get your own copy, please visit TheExcelAddict.com or send a blank email to theexceladdict(AT)aweber(DOT)com
If you would like to share this newsletter with others...
1) Forward this newsletter by email, but first delete the unsubscribe link at the very bottom so you don't get accidentally unsubscribed
2) Ask your friend/colleague to visit TheExcelAddict.com or send a blank email to theexceladdict(AT)aweber(DOT)com
3) Post a link to TheExcelAddict.com in a company newsletter or website
|"Spreadsheets Tips From An Excel Addict" is a weekly publication of TheExcelAddict.com.|
Copyright Francis J. Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630