Microsoft Office Tutorials and References
In Depth Information
Removing Excess Spaces
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
dialog box.
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:
=A1<>TRIM(A1)
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.
A yellow fill color is a good choice.
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, every cell that contains excess spaces and is within the range
you selected in Step 1 displays the formatting of your choice. You can then easily spot these cells
and remove the spaces.
Because of the way the TRIM function works, the formula in Step 4 also applies the
conditional formatting to all numeric cells. A slightly more complex formula that
doesn’t apply the formatting to numeric cells is
=IF(NOT(ISNONTEXT(A1)),A1<>TRIM(A1))
 
Search JabSto ::




Custom Search