Microsoft Office Tutorials and References

In Depth Information

**Creating and Editing Formulas**

Comparisons of similar data types work as you would expect. For values—including

constants as well as references and formulas that evaluate to values—higher numbers are

greater than lower and positive numbers are greater than negative numbers. Dates can be

compared to other dates or to numbers. (For the technical reasons, see the discussion of

date serial numbers in “Date and Time Functions” on page 368.) Comparing text uses

alphabetical order, with letters before numbers and case ignored. (In other words, “District” and

“district” are considered equal.)

Concatenating Text

You can combine two or more text values to produce a single text value. What makes this

magic possible is the concatenation operator—an ampersand (&). A very common use of

this operator is in address lists where first and last names are in separate columns. To

combine the names into a single cell, use a formula like the one shown here:

This sample uses two concatenation operators and illustrates that you can insert literal text

(like the space between the two names) as long as you enclose it in quotation marks. So,

if you have a list of opening and closing dates for a school, with one such date in cell C12,

you could convert it to a more readable text value by using the following formula:

="Closing day: "&TEXT(C12,"mmmm dd, yyyy")

The formula grabs the date from the referenced cell, reformats it, and appends the

introductory text. Compare the contents of the formula bar to the formula’s result, as displayed

in bold here: