Formatting number in a text output to show thousands separator

Formatting number inside text output in Excel will save the aspect of the report generated. Sometimes you need to optimize the time taken to update a excel spreadsheet report by using formulas which output text and numbers. If you use only the concatenating operator the result will be that the number inside the text is not formatted and the text doesn’t look very well.

Formatting to show thousands separator inside text output

All you need to do is use the TEXT() function, which formats the value given in the way you want. Just put inside the value to format and then the pattern like in the image below:

Formatting with thousands separator

Formatting number to show only thousands

If you need to show only thousands instead of units inside the text you need to use the pattern “$#,##0,” in the text() formula.

Other uses of text() function

The same way dates or other types of information should be treated by giving the formula the right pattern to format.

 

Write a Reply or Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.