Excel 2010 Bible

Part II: Working with Formulas and Functions

234

You’ll find that the previous formula returns TRUE even though the contents of the two cells are

not really the same — the comparison is not case-sensitive.

Often, you don’t need to worry about the case of the text. If you need to make an exact, case-sensitive

comparison, though, use the EXACT function. The following formula returns TRUE only if cells A1

and A2 contain exactly the same entry:

=EXACT(A1,A2)

When you compare text, be careful with trailing space characters, which are often difficult to iden-

tify. The following formula returns FALSE because the first string contains a trailing space:

=EXACT(“Canada “,”Canada”)

Joining two or more cells.........................................................................................

Excel uses an ampersand (&) as its concatenation operator. Concatenation is simply a fancy term

that describes what happens when you join the contents of two or more cells. For example, if cell

A1 contains the text Tucson and cell A2 contains the text Arizona, the following formula will

return TucsonArizona:

=A1&A2

Notice that the two strings are joined together without an intervening space. To add a space

between the two entries (to get Tucson Arizona), use a formula like this one:

=A1&” “&A2

Or, even better, use a comma and a space to produce Tucson, Arizona:

=A1&”, “&A2

If you’d like to force the second string to be on a new line, concatenate the strings using

CHAR(10), which inserts a line-break character. Also, make sure that you apply the Wrap Text

format to the cell. The following example joins the text in cell A1 and the text in cell B1, with a

line break in between:

=A1&CHAR(10)&B1

Tip
To apply Wrap Text formatting, select the cells and then choose Home ➪ Alignment ➪ Wrap Text. n

You can also concatenate characters returned by the CHAR function. The following formula returns

the string Stop by concatenating four characters returned by the CHAR function:

=CHAR(83)&CHAR(116)&CHAR(111)&CHAR(112)