Excel, of course, is best known for its ability to crunch numbers. However, it is also quite versatile when it comes to handling text. As you know, Excel enables you to enter text for things such as row and column headings, customer names and addresses, part numbers, and just about anything else. And, as you might expect, you can use formulas to manipulate the text contained in cells.
This chapter contains many examples of formulas that use functions to manipulate text. Some of these formulas perform feats you may not have thought possible.
When you enter data into a cell, Excel immediately goes to work and determines whether you're entering a formula, a number (including a date or time), or anything else. Anything else is considered text.
| <> | Note | <><> |
A single cell can hold up to 32,000 characters. To put things into perspective, this chapter contains about 30,000 characters. I certainly don't recommend using a cell in lieu of a word processor, but you really don't have to lose much sleep worrying about filling up a cell with text.
As I mentioned, Excel distinguishes between numbers and text. If you want to "force" a number to be considered as text, you can do one of the following:
- Apply the Text number format to the cell. Select Text from the Number Format drop- down list, which can be found at Home
- Precede the number with an apostrophe. The apostrophe isn't displayed, but the cell entry will be treated as if it were text.
Even though a cell is formatted as Text (or uses an apostrophe), you can still perform some mathematical operations on the cell if the entry looks like a number. For example, assume cell A1 contains a value preceded by an apostrophe. The formula that follows will display the value in A1, incremented by 1:
=A1+1
The formula that follows, however, will treat the contents of cell A1 as 0:
=SUM(A1:A10)
If you import data into Excel, you may be aware of a common problem: Sometimes, the imported values are treated as text. Here's a quick way to convert these non- numbers to actual values. Activate any empty cell and choose Home
In some cases, treating text as a number can be useful. In other cases, it can cause problems. Bottom line? Just be aware of Excel's inconsistency in how it treats a number formatted as text.
Note | 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 choose Convert to Number. Background error checking is controlled in the Excel Options dialog box. Choose Office |