Format Cells > Custom to show "21564" as "21000+"

[Rainbow_P]

Good day!


Format Cells > Number > Custom

For custom format, for example, with A1 cell value >999, like 21564, what would be the format to apply to show it as "21000+"?

I've tried "[>999]#,#,"000+";#". It shows "22000+".

Thank you.

 

[Oaktree]

I believe all of your number formatting options round (as opposed to truncate).

You could do this by adding another column (and hiding the source column) with something like:

=IF(A2>999,TRUNC(A2,-3)&"+",A2)

 

[Rainbow_P]

@Oaktree, thanks for the reply!

However I am looking for a solution in Custom formatting.

 

[Tetra201]

Try the following workaround:
- in the Format Cells > Number > Custom > Type, type [>999]#"000+"###;#
- place the cursor between the double quotation mark and the ###;#
- press and hold Ctrl, then press j -- the ###;# part will hide
- click OK
- enable text wrapping in the cell
- adjust row height to see only 21000+

 

[Rainbow_P]

@Tetra201 , thanks for the amazing solution!

 

[snjpverma]

Try the following workaround:
- in the Format Cells > Number > Custom > Type, type [>999]#"000+"###;#
- place the cursor between the double quotation mark and the ###;#
- press and hold Ctrl, then press j -- the ###;# part will hide
- click OK
- enable text wrapping in the cell
- adjust row height to see only 21000+

Tetra, where did you learn so deeply about these custom format rules. I too want to learn this. Could you guide me?

 

[Tetra201]

Please take a look here. Ctrl-j is a Line Feed control character (aka LF or Chr(10)).

 

[snjpverma]

Please take a look here. Ctrl-j is a Line Feed control character (aka LF or Chr(10)).

Thank you Sir