Text and values in one cell


Did you know that you could combine text and values in a single cell? For example, if cell A12 contains the value 1435 and you type ="Total: " &A12 into another cell, this second cell will display "Total: 1435". The ampersand is a concatenation operator that joins the text with the contents of cell A12.

Note: applying a number format to the cell containing the formula has no effect, because the cell contains text, not a value. As a workaround, use the following Text() function:

="Total: " &TEXT(A12,"$#,##0.00")

This function will yield Total: $1,435.00.

The second argument for the Text() function consists of a standard Excel number-format string. Here's an example that uses the Now() function (be sure to type it as one sequence, without any line breaks):

="Report printed on " &TEXT(NOW(),"mmmm d, yyyy at h:mm AM/PM")

Caption: The formula in cell A1 combines text with a formatted value ù in this case, a date and time

- John Walkenbach


Category: spreadsheet
Issue: May 1999

These Web pages are produced by Australian PC World © 1999 IDG Communications