Combining text and formatted values

Excel 2016
The Excel TEXT function enables you to specify numbers and date format.

Although this function may appear to have dubious value, it does serve some useful purposes.

For example, the formula in cell D4 is: = "The net profit is " & B3.

Dubious value in Excel 365

This formula essentially combines a text string with the contents of cell B3 and displays the result. Note, however, that the contents of B3 are not formatted in any way. You might want to display the contents of B3 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.

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:

Dubious formatted in Excel 365

The second argument for the TEXT function consists of a standard Excel number format string. You can enter any valid number format string for this argument.

Here's another example that uses the NOW function (which returns the current date and time). The TEXT function displays the date and time, nicely formatted.

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

The formula might display the following:

Formula in Excel 365

See also this tip in French: Combiner du texte et valeurs formatées.

Please, disable AdBlock and reload the page to continue

Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.