Two Excel Functions to Format Numbers as Text

Using numbers in automated text sentences can be frustrating. Typically you don’t want to display decimals, but you do want to use the comma format.

When you use numbers in sentences by themselves you get no formatting. See cell C3 in the image below. The formula used in cell C3 is

=A3&" "&B3

Text Number Example

You can use the TEXT function to format numbers but it takes quite a few keystrokes to achieve the desired format (see examples at the bottom of the post). There are two functions that can make the process easier and quicker.

FIXED Function

This function allows you to display numbers without decimals and with the comma format. The formula used in cell D3 is

=A3&" "&FIXED(B3,0)

The zero at the end of the FIXED function instructs Excel to apply zero decimal places. If you leave out the zero it defaults to two decimal places.

DOLLAR Function

Similar to the FIXED function, but it also includes the $ sign with the number. The formula for cell E3 is

=A3&" "&DOLLAR(B3,0)

Leaving out the ,0 at the end will display two decimal places.

Both of these functions are shorter and easier to enter than than the TEXT function versions that are shown below.

TEXT Function

Cell D3

=A3&" "&TEXT(B3,"#,##0")

Cell E3

=A3&" "&TEXT(B3,"$#,##0")

Having to use the quotation marks plus the multiple symbols makes these slower to enter.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *