Page 252 - Excel 2007 Bible

17_044039 ch12.qxp 11/21/06 11:05 AM Page 209
Creating Formulas That Manipulate Text
Inserting special characters
f you need to insert special characters not found on your keyboard, you can use the Symbol dialog box
I(which is accessed using Insert ➪ Text ➪ Symbol). This dialog box simplifies inserting special characters
(including Unicode characters) into cells. For example, you may want to display the Greek letter pi (π) in your
spreadsheet. Access Excel’s Symbol dialog box and select the Symbol font (see the accompanying figure).
Examine the characters, locate the pi character, and click Insert. You’ll see (in the Character Code area of the
Symbol dialog box) that this character has a numerical code of 112.
In addition, Excel has several built-in AutoCorrect symbols. For example, if you type (c), Excel converts it to a 12
copyright symbol. To see the other symbols that you can enter this way, display the AutoCorrect dialog box. To
display this dialog box, choose Office ➪ Excel Options and select the Proofing tab in the Excel Options dia-
log box. Then click the AutoCorrect Options button. You can then scroll through the list to see which autocor-
rections are enabled (and delete those that you don’t want).

If you find that Excel makes an autocorrection that you don’t want, press Ctrl+Z to undo the autocorrection.

Assume cell A1 contains the letter A (uppercase). The following formula returns the letter a (lowercase):

=CHAR(CODE(A1)+32)
This formula takes advantage of the fact that the alphabetic characters all appear in alphabetical order
within the character set; lowercase letters follow uppercase letters (with a few other characters tossed in
between). Each lowercase letter lies exactly 32 character positions higher than its corresponding uppercase
letter.
Determining whether two strings are identical
You can set up a simple logical formula to determine whether two cells contain the same entry. For exam-
ple, use this formula to determine whether cell A1 has the same contents as cell A2:
=A1=A2

209