 |
|
| TheExcelAddict.com | |
| October 18, 2018 | |
| Hi fellow Excel Addict, | |
The
weather in Newfoundland is cool and windy in
October. Here is a picture I took at 'Mad Rocks'
in Bay Roberts, Newfoundland a couple of weeks
ago. This is only a 10 minute drive from where I
grew up in Brigus — rugged but beautiful. Today my 'Excel in Minutes' tip is 'Cleaning Nasty Things From Your Worksheets'. I hope you like it. I always appreciate it when you share my tips with other Excel users that you know. Keep on Excelling, Francis Hayes (The Excel Addict) Email: fhayes[AT]TheExcelAddict.com  |
|
|
|
|
 |
|
|
|
|
|
|
| |
|
|
|
|
If
you
have a favourite quote, send it to me and I
may post it in my newsletter.
|
|
 |
|
| How
To Clean Nasty Things From Your Worksheets |
|
Blank Cells Aren't Always Blank Many users new to Excel soon discover that if you press the spacebar on your keyboard, the contents of a cell seem to disappear. Too often they develop a bad habit of using this technique to "clear"the contents of cells. The fact is, a blank space in a cell is not the same as a blank cell. Excel sees a blank space as if it was a text character such as "a" or "b". Blank spaces can cause havoc in your worksheets. Many formulas, functions and charts may not work or may give erroneous results if cells containing a blank space are included in its calculations. Filtering and sorting will frustrate you and all sorts of other nasty things may result. Bottom line: Never clear cells in a worksheet using the spacebar! Iknow YOU wouldn't do this but you may be working with worksheets from other users. So if you find a spreadsheet with this issue here's a solution. To find if a worksheet contains cells with just a single blank space, try this... 1) Press CTRL+H to bring up the 'Find and Replace' dialog;  3) Make sure the 'Replace with' field is empty; 4) Select the 'Match entire cell contents' option. If you don't see that option, click the Options>> button; 4) Click the Replace All button. All cells containing just a single blank space will be cleared. Other nasty things that may be hiding in your worksheets: Remove Multiple blank spaces  It
is
also possible that your worksheet contains cells
with two or more blank spaces, either coming
from imported data or someone has typed multiple
spaces. If that's the case, follow the procedure
described above but instead type 2 blank spaces
in the 'Find what' field and leave the 'Match
entire cell contents' option unchecked.You may find that it's better to do this (multiple space) procedure first, then follow the above procedure (using the 'Match entire cell contents' option) to remove the cells with just a single space. Remove 'Non-breaking Space' characters Sometimes the above steps don't clear some blank spaces. It is likely that these cells contain 'non-breaking space' (NBSP) characters. NBSPs are characters (with a decimal value of 160) that are commonly used in web pages. When data is copied or imported from a web source, it often contains NBSPs. Although, it's possible that some other character may be included in your data, i find that in the vast majority of cases it is the NBSP that causes the most problems. To get rid of these NBSP characters in an Excel worksheet... 1) Press CTRL+H to bring up the 'Find and Replace' dialog;  3) Make sure the 'Replace with' field is empty**; 4) Click the Replace All button. All NBSP characters will be removed. **If you find that this procedure removes spaces between words, you may need to use a blank space in the "Replace with' field instead of leaving it blank. TRIM, CLEAN Additionally, there are two Excel functions that can help you clean data (not just 'blank' cells) that contains leading or trailing spaces. The TRIM function removes all extra spaces (except NBSPs) from text except for single spaces between words e.g. =TRIM(E1). The CLEAN function removes non printable characters from text e.g. CLEAN(E1). You can also combine these two functions e.g. =TRIM(CLEAN(E1)). If you use these functions, you will need to Copy and Paste Values to replace your original data. Depending on your particular situation, you may require one of more of the above solutions and maybe in a different sequence to clean up the data on your worksheet. |
|
|
|
|
| 'Excel in Minutes' with The Excel Addict | |
| How To Clean Nasty Things From Your Worksheets | |
| If you've found this tip helpful, please share it. | |
|
|
|
|
|
|
|
|
| Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are EVER not completely satisfied with anything I recommend, please let me know and you will get your money...GUARANTEED. You can't lose. | |
|
"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 |
