Chapter 5: Manipulating Text

 Download CD Content

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.

A Few Words about Text

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 

You may hear the term string used instead of text. You can use these terms interchangeably. Sometimes, they even appear together, as in text string.

How Many Characters in a Cell?

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.

Numbers as 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 Number. If you haven't applied other horizontal alignment formatting, the value will appear left-aligned in the cell (like normal text).

• 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) 

When a Number Isn't Treated as a Number

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 Clipboard Copy. 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 option and click OK. By "adding zero" to the text, you force Excel to treat the non- numbers as actual values.

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 Excel Options and navigate to the Error Checking section of the Formulas tab.