FREE Excel Tips Newsletter from TheExcelAddict.com
Subscribe here to get more tips like this every week

The Excel Addict - Help with Excel 2013, 2010, 2007, 2003

July 14, 2016

 
Greetings from The Excel Addict
Hi fellow Excel Addict,

Francis Hayes - TheExcelAddict.comToday's tip is one that a lot of Excel users have a need for. I probably recommend this tip several times every month.

With our data coming from so many different sources, often we find extra blank spaces and other odd characters embedded in our data that shouldn't be there. It can be frustrating to find and get rid of them. I hope that my tip today will make things a lot easier for you.

I
f you missed my 'Excel in Seconds' newsletter on Tuesday, I showed you 'How to Delete A Workbook Without Leaving Excel'. You can read the tip here.

W
ishing you another great day of Excelling,
Francis Hayes (The Excel Addict)
Email:  fhayes[AT]TheExcelAddict.com





If you missed my last newsletter, you can click here to view it online.
 

 
TheExcelAddict.com Quote of the Day

"Most people give up just when they’re about to achieve success.
They quit on the one yard line.
They give up at the last minute of the game
one foot from a winning touchdown."

-- Ross Perot --

If you have a favourite quote, send it to me and I may post it in my newsletter.


Today's Microsoft Excel Tip

Cleaning Nasty Things From Your Worksheet


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!

I know 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;

Find And Replace Remove Spaces in Microsoft Excel 2007 2010 2013 2016 365
2) Click in the 'Find what' field and type a single blank space;

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
Remove Multiple Blank Spaces in Microsoft Excel 2007 2010 2013 2016 365It 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;

Find And Replace Remove Nbsp Character Spaces in Microsoft Excel 2007 2010 2013 2016 365
2) Click in the 'Find what' field, hold down the ALT key and type 0160 using your numeric keypad (not the top row of number keys) to enter a NBSP character;

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.

FREE Excel Tips Newsletter from TheExcelAddict.com
Subscribe here to get more tips like this every week

Thanks for supporting this newsletter and website

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