Microsoft Office Tutorials and References
In Depth Information
How Excel Identifies Text
Types of Data
How Excel Identifies Text
If your cell meets any of the following criteria, Excel automatically treats the content
as ordinary text :
• It contains any letters . Thus, C123 is text, not a number.
• It contains any punctuation that Excel can’t interpret numerically .
Punctuation allowed in numbers and dates includes the comma (,), the decimal point
(.), and the forward slash (/) or dash (-) for dates. When you type in any other
punctuation, Excel treats the cell as text. Thus, 14! is text, not a number.
Occasionally, Excel reads your data the wrong way. For example, you may have a
value—like a social security number or a credit card number—that’s made up entirely
of numeric characters but that you want to treat like text because you don’t ever want
to perform calculations with it. In this case, Excel doesn’t know what you’re up to,
and so it automatically treats the value as a number. You can also run into problems
when you precede text with the equal sign (which tells Excel that you have a formula
in progress), or when you use a series of numbers and dashes that you don’t intend
to be part of a date (for example, you want to enter 1-2-3 but you don’t want Excel to
read it as January 2, 2003—which is what it wants to do).
In all these cases, the solution’s simple. Before you type the cell value, start by typing
an apostrophe (‘). The apostrophe tells Excel to treat the cell content as text. Figure
14-31 shows you how it works.
To have Excel treat any number, date, or time as text, just
precede the value with an apostrophe (you can see the
apostrophe in the formula bar but not in the cell). This
worksheet shows the result of typing 1-2-3, both with and
without the initial apostrophe. When you store 1-2-3 as
text, Excel left-aligns it, as if it were an all-text cell (and
puts a tiny green triangle in the corner of the cell to let you
know you may have made a mistake). The date, on the
other hand, is right-aligned.
When you precede a numeric value with an apostrophe, Excel checks out the cell to
see what’s going on. When Excel determines that it can represent the content as a
number, it places a green triangle in the top left corner of the cell and gives you a few
options for dealing with the cell, as shown in Figure 14-32.
Tip: When you type in either false or true (using any capitalization you like), Excel automatically
recognizes the data type as Boolean value instead of text, converts it to the uppercase word FALSE or TRUE, and
centers it in the cell. If you want to make a cell that contains false or true as text and not as Boolean data,
start by typing an apostrophe (‘) at the beginning of the cell.