Microsoft Office Tutorials and References
In Depth Information
If background error checking is turned on, Excel flags numbers preceded by an
apostrophe (and numbers formatted as Text) with a Smart Tag. You can use this Smart Tag
to convert the “text” to an actual value. Just click the Smart Tag and select Convert to
Number. Background error checking is controlled in the Excel Options dialog box.
Choose File➜Options and navigate to the Error Checking section of the Formulas tab.
Excel has an excellent assortment of worksheet functions that can handle text. For your
convenience, the Function Library group on the Formulas tab includes a Text drop-down list that
provides access to most of these functions. A few other functions that are relevant to text
manipulation appear in other function categories. For example, the ISTEXT function is in the
Information category (Formulas
Refer to Appendix A for a listing of the functions in the Text category.
Most of the functions in the Text category are not limited for use with text. In other words, these
functions can also operate with cells that contain values. Excel is very accommodating when it
comes to treating numbers as text and text as numbers.
The examples in this section demonstrate some common (and useful) things that 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 can 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.
The ISTEXT function takes a single argument, returning 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:
You can also use the TYPE function. The TYPE function takes a single argument and returns a
value that indicates the type of data in a cell. If cell A1 contains a text string, the formula that
follows returns 2 (the code number for text):