Custom format not working (for millions notation)

Hi all

Something easy turned out to be impossible. I want 2000000 (2 million) to display as $2M. What I did was Format Cells > Number > Custom and typed $#.##0,,"M". Then my value is displayed as $2000000.,,M.

I've also tried "$#.##0,,"M". With and without "". Nothing. The closest I can get is when I use variations of #,######"M". This displays the value as 2,000000M, which defeats the purpose.

I've also tried numerous other variations as available on Google, but clearly I'm missing something. It's driving me nuts.

Try:

Please Login or Register  to view this content.

Try the attached. How does cell A1 display to you?

$#,,"M"

or this if your regional setting for Thousands separator is a period
$#.."M"

Originally Posted by PinkGecko

@OllyXLS:

Finally! Thank you. I copied your $# ##0 "M". But it's different than all the suggestions of $#.."M" and $#,##0,,"M". The cells I'm applying it to, is a monetary value multiplied by a number, so not sure what the issue was.

@Andy Pope: we don't use a thousands seperator at all, perhaps that's why OllyXLS's way worked?

Great - what this tells me is that your local settings use a space as a thousand seperator. The format applied in my attachment was $#,##0,,"M" - and Excel has translated that format into your locale settings.

Glad it worked for you.

we don't use a thousands seperator at all

you do, but as OllyXLS says you use space. It's the reason I mentioned "regional setting", although I did not guess it was a space rather than comma or period