How to Add a Thousand Separator by Formula

When working with large numbers, you often need to format them with the thousand separators. You can check here if you just want to format numbers with a thousand separator.

However, when merging numbers with text, the numbers will be in different cells, and you cannot format them. If that is the case, please use the following formula to add a thousand separator.

=TEXT(B2,"0,000.00")&" "&A2

Where "0,000.00" is to add a thousand separator and to keep 2 decimals. However, when a number is less than 1000, this will force the number to have "0,000.00" format. For example, 0.16 will display as 0,000.16, which is not what we want.

To display without 0s when the number is less than 1000, and to add a thousand separator, please change "0,000.00" to "#,###.##", where 0.16 will display as ".16".

=TEXT(B2,"#,###.##")&" "&A2

=TEXT(B2,"#,###.00")&" "&A2

To change thousand into "K", please check how to format thousand into K by the formula.

One Response

  1. Mohammad Tanvir October 15, 2021

Leave a Reply