Excel 2010 Bible

Part IV: Using Advanced Excel Features

652

For example, the following formula returns the number of nonempty cells in range A1:A10. If you

“erase” any of these cells by using the spacebar, these cells are included in the count, and the for-

mula returns an incorrect result.

=COUNTA(A1:A10)

If your formula doesn’t ignore blank cells the way that it should, check to make sure that the blank

cells are really blank cells. Here’s how to search for cells that contain only blank characters:

1. Press Ctrl+F to display the Find and Replace dialog box.


2. Click the Options button to expand the dialog box so it displays additional options.


3. In the Find What box, enter . That’s an asterisk, followed by a space, and followed
   by another asterisk.


4. Make sure the Match Entire Cell Contents check box is selected.


5. Click Find All. If any cells that contain only space characters are found, Excel will list
   the cell address at the bottom of the Find and Replace dialog box.

Extra space characters

If you have formulas or use procedures that rely on comparing text, be careful that your text

doesn’t contain additional space characters. Adding an extra space character is particularly com-

mon when data has been imported from another source.

Excel automatically removes trailing spaces from values that you enter, but trailing spaces in text

entries are not deleted. It’s impossible to tell just by looking at a cell whether it contains one or

more trailing space characters.

The TRIM function removes leading spaces, trailing spaces, and multiple spaces within a text

string. Figure 32.1 shows some text in column A. The formula in B1, which was copied down the

column is

=TRIM(A1)=A1

This formula returns FALSE if the text in column A contains leading spaces, trailing spaces, or

multiple spaces. In this case, the word Dog in cell A3 contains a trailing space.

FIGURE 32.1

Using a formula to identify cells that contain extra space characters.