Site icon ExcelNotes

How to Format Thousand into K or Word Thousand by Formula

When working with large numbers, you often need to add a thousand separator. Also, in some other situations, you may want to change the thousand to K instead of many 0s. For example, 10,000 to 10K.

1. Turn into K

Please use the formula below to format thousand to "K":

=TEXT(A2,"#.00,K")

Where "#.00" is to keep 2 decimals.

If you also want to add a thousand separator when converting thousands into "K", please use the formula below.

=TEXT(A2,"#,###.0,K")

2. Turn into work of Thousand

Another way is to round the large numbers into nearest thousands, then combine with the work of "Thousand" with the following formula.

=TEXT(A2/1000,"#,###.##")&" K"

or to add the word of "thousand" after the number.

=TEXT(A2/1000,"#,###.##")&" thousand"

Please note that the formula will turn to ".K" or ".thousand" when the number is less than 5 and you need to keep 2 decimals. To avoid this to happen, you can combine IF function to condition out in Excel 2016 or earlier version.

=IF(A2<5,"",IF(A2>=5,TEXT(A2/1000,"#,###.##")&" K"))

If in Excel 2019 and after, IFS function makes the condition much easier.

=IFS(A2<5,"",A2>=5,TEXT(A2/1000,"#,###.##")&" K")

Exit mobile version