Microsoft Office Tutorials and References
In Depth Information
Tip 62: Identifying Excess Spaces
If your worksheet contains thousands of text entries — and you need to perform comparisons using
that text — you may want to identify the cells that contain excess spaces and then fix those cells. The
term excess spaces means a text entry that contains any of the following:
➤ One or more leading spaces
➤ One or more trailing spaces
➤ Two or more consecutive spaces within the text
One way to identify this type of cell is to use conditional formatting. To set up conditional formatting
to identify excess spaces, follow these steps:
1. Select all text cells to which you want to apply conditional formatting.
2. Choose Home ➜ Conditional Formatting ➜ New Rule to display the New Formatting Rule
3. In the top part of the dialog box, select the option labeled Use a Formula to Determine
Which Cells to Format.
4. Enter a formula like the following in the bottom part of the dialog box (see Figure 62-3):
Note: This formula assumes that cell A1 is the upper-left cell in the selection. If that’s not the
case, substitute the address of the upper-left cell in the selection you made in Step 1.
5. Click the Format button to display the Format Cells dialog box and select the type of
formatting you want for the cells that contain excess spaces — for example, a yellow fill color.
6. Click OK to close the Format Cells dialog box, and click OK again to close the New Formatting
Rule dialog box.
After you complete these steps, each cell that contains excess spaces and is within the range you
selected in Step 1 is highlighted with the formatting of your choice. You can then easily spot these
cells and remove the spaces.