Excel 2010 Bible

Chapter 11: Creating Formulas That Manipulate Text

235

Here’s a final example of using the & operator. In this case, the formula combines text with the

result of an expression that returns the maximum value in column C:

=”The largest value in Column C is “ &MAX(C:C)

Note
Excel also has a CONCATENATE function, which takes up to 255 arguments. For example:

=CONCATENATE(A1,B1,C1,D1)

This function simply combines the arguments into a single string. You can use this function if you like, but
using the & operator results in shorter formulas. n

Displaying formatted values as text

The TEXT function enables you to display a value in a specific number format. Figure 11.2 shows

a simple worksheet. The formula in cell D3 is

=”The net profit is “ & B3

FIGURE 11.2

The formula in D3 doesn’t display the formatted number.

This formula essentially combines a text string with the contents of cell B3 and displays the result.

Note, however, that the formula displays the contents of B3 as a raw value (no formatting). To

improve readability, you might want to display the contents of B3 by using a Currency number

format.

Note
Contrary to what you might expect, applying a number format to the cell that contains the formula has no
effect. This is because the formula returns a string, not a value. n

Here’s a revised formula that uses the TEXT function to apply formatting to the value in B3:

=”The net profit is “ & TEXT(B3,” $#,##0”)

This formula displays the text along with a nicely formatted value:

The net profit is $230,794