Microsoft Office Tutorials and References

In Depth Information

**Editing formulas**

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
.