Custom number format in Millions - How to remove leading decimal if round

[netuser]

Hi,

I have been searching for hours on google and did not found the solution to my issue (all solution are for decimal value, in my case it is just a display not real decimal value). I am using following custom number format to show amounts is millions :

0.#,, "M";-0.#,, "M";"-"

All is working fine if I dont have round number (1,100,000) but for round amount like 1,000,000 it show as 1. M

How to avoid leading orphan decimal point if amount is round ?

Thanks

 

[shg]

Use conditional formatting with the condition =int(a1)=a1 and a number format sans decimal.

 

[netuser]

This break the decimal value, if I have 1,100,000 it is now rounded to 1 M instead of 1.1 M

 

[netuser]

Just a thought the above solution (int(a1)=a1 ) can never work as my real value is never a decimal value it is just a display even if 1,100,000 will show 1.1 M with custom format, it is still a value that is always INT

 

[netuser]

Thanks shg for putting me on right path, I was able to solve my problem by dividing the number by 1 million in conditional formating : =INT((F20)/1000000)=(F20)/1000000

 

[shg]

You're welcome.