Page 250 - Excel 2007 Bible

17_044039 ch12.qxp 11/21/06 11:05 AM Page 207
Creating Formulas That Manipulate Text
A few other functions that are relevant to text manipulation appear in other function categories.
CROSS-REF
CROSS-REF
functions in the Insert Function dialog box. Activate an empty cell, and choose Formulas ➪
Function Library ➪ Insert Function. In the Insert Function dialog box, select the Text category and scroll
through the list. To find out more about a particular function, click the Help On This Function link.
Most text functions are not limited to use with text; they can also operate with cells that contain values.
You’ll find that Excel is very accommodating when it comes to treating numbers as text and text as
numbers.
The examples discussed in this section demonstrate some common (and useful) things you can do with
text. You may need to adapt some of these examples for your own use.
Determining whether a cell contains text
In some situations, you may need a formula that determines the type of data contained in a particular cell.
For example, you may use an IF function to return a result only if a cell contains text. The easiest way to
make this determination is to use the ISTEXT function.
If you’re browsing through the Text functions, you won’t find the ISTEXT function. The
NOTE Refer to Appendix A for a listing of the functions in the Text category — or you can peruse these 12
NOTE
ISTEXT function is in the Information category, which is accessed from the More control in
the Function Library group.
The ISTEXT function takes a single argument and returns TRUE if the argument contains text and FALSE if
it doesn’t contain text. The formula that follows returns TRUE if A1 contains a string:
=ISTEXT(A1)
Working with character codes
Every character you see on your screen has an associated code number. For Windows systems, Excel uses
the standard ANSI character set. The ANSI character set consists of 255 characters, numbered (not surpris-
ingly) from 1 to 255.
Figure 12.1 shows a portion of an Excel worksheet that displays all of the 255 characters. This example
uses the Wingdings 3 font. (Other fonts may have different characters.)
The companion CD-ROM includes a copy of this workbook, which also includes some simple
ON the CD-ROM VBA macros that enable you to display the character set for any font installed on your system.
ON the CD-ROM
The file is named character set.xlsm.
Two functions come into play when dealing with character codes: CODE and CHAR. These functions may
not be very useful by themselves, but they can prove quite useful in conjunction with other functions. I dis-
cuss these functions in the following sections.

207