Microsoft Office Tutorials and References
In Depth Information
Removing Excess Spaces
Removing Excess Spaces
A common type of error involves something that you can’t even see: a space character. Consider
the example shown in Figure 198-1. Cell B2 contains a formula that looks up the color name in cell
B1 and returns the corresponding code from a table. The formula is
=VLOOKUP(B1,D2:E9,2,FALSE)
Figure 198-1: A simple lookup formula returns the code for a color entered in cell B1.
In Figure 198-2, the formula in cell B2 returns an error — indicating that Red wasn’t found in the
table. Hundreds of thousands of Excel users have spent far too much time trying to figure out
why this sort of thing doesn’t work. In this case, the answer is simple: Cell D7 doesn’t contain the
word Red . Rather, it contains the word Red followed by a space. To Excel, these text strings are
completely different.
Figure 198-2: The lookup formula can’t find the word Red in the table.
If your worksheet contains thousands of text entries — and you need to perform comparisons
using that text — you might 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, or two or more consecutive spaces.
One way to identify this type of cell is to use conditional formatting.
To set up conditional formatting, follow these steps:
 
Search JabSto ::




Custom Search