Part II: Working with Formulas and Functions
230
If you need to display lots of text in a worksheet, consider using a text box. Choose Insert ➪
Text ➪ Text Box, and start typing. Working with large amounts of text in a text box is easier than edit-
ing cells. In addition, you can easily move, resize, or change the dimensions of a text box. However, if
you need to work with the text using formulas and functions, the text must reside in cells.Text Functions ..................................................................................................................
Excel has an excellent assortment of worksheet functions that can handle text. You can access these
functions just where you’d expect: from the Text control in the Function Library group of the
Formula tab.A few other functions that are relevant to text manipulation appear in other function categories.Cross-Reference
See Appendix A for a listing of the functions in the Text category. Or, you can peruse these 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. n
If you import data into Excel, you may be aware of a common problem: Sometimes the imported values
are treated as text.Depending on your error-checking settings (as specified on the Formula tab of the Excel Options dialog
box), Excel may display a Smart Tag to identify numbers stored as text. If the cell contains a Smart Tag,
you’ll see a small rectangle in the upper-left corner of the cells. Activate the cell, and you can respond
to the Smart Tag. To force the number to be treated as an actual number, select Convert to Number
from the Smart Tag list of options.If the Smart Tag isn’t displayed, here’s another way to convert these non-numbers to actual values.
Activate any empty cell and choose Home ➪ Clipboard ➪ Copy (or press Ctrl+C). Then select the
range that contains the values you need to fix. Choose Home ➪ Clipboard ➪ Paste Special. In the Paste
Special dialog box, select the Add operation and then click OK. This procedure essentially adds zero to
each cell — and, in the process, forces Excel to treat the non-numbers as actual values.When a Number Isn’t Treated as a Number