Microsoft Office Tutorials and References
In Depth Information
You must enclose numeric text strings in quotation marks. For example, if you type the
formula =$1234+$123 , Excel displays an error message. (The error message also offers to
correct the error for you by removing the dollar signs.) But the formula ="$1234"+"$123"
produces the result 1357 (ignoring the dollar signs). When Excel performs the addition, it
automatically translates numeric text entries into numeric values.
For more information about number-formatting characters, see “Formatting as you type” in
Chapter 9, “Worksheet formatting techniques.”
About text values
The term text value refers to any entry that is neither a number nor a numeric text value (as
explained in the previous section); Excel treats the entry as text only. You can refer to and
manipulate text values by using formulas. For example, if cell A1 contains the text First and
you type the formula =A1 in cell A10, cell A10 displays First .
For more information about manipulating text with formulas, see “Understanding text
functions” in Chapter 14.
You can use the & (ampersand) operator to concatenate , or join, several text values.
Extending the preceding example, if cell A2 contains the text Quarter and you type the formula
=A1&A2 in cell A3, then cell A3 displays FirstQuarter . To include a space between the two
strings, change the formula to =A1&" "&A2. This formula uses two concatenation operators
and a literal string , or string constant (in this case, a space enclosed in quotation marks).
You can use the & operator to concatenate strings of numeric values as well. For example,
if cell A3 contains the numeric value 867 and cell A4 contains the numeric value 5309 , the
formula =A3&A4 produces the string 8675309 . This string is left-aligned in the cell because
it’s considered a text value. (Remember, you can use numeric text values to perform any
mathematical operation as long as the numeric string contains only the numeric characters
listed in the previous section.)
Finally, you can use the & operator to concatenate a text value and a numeric value. For
example, if cell A1 contains the text January and cell A3 contains the numeric value 2009 ,
the formula =A1&A3 produces the string January2009 .