Microsoft Office Tutorials and References
In Depth Information
Tip 62: Identifying Excess Spaces
Identifying Excess Spaces
A common type of spreadsheet error involves something that you can’t even see: a space character.
Consider the example shown in Figure 62-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
Figure 62-1: A simple lookup formula returns the code for a color entered in cell B1.
In Figure 62-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 62-2: The lookup formula can’t find the word Red in the table.