Convert number to Million Currency format

[Yossy]

Any Help will be appreciated. I have this 345,000 and I want to get this
formatted to this .345 MM

 

[Gord Dibben]

Custom Format .#,###,," MM"

That's dot#comma###commacomma


Gord Dibben MS Excel MVP

 

[Rick Rothstein]

I'm not sure that works correctly (my XL2003 SP3 version removes the comma
and outputs 4 decimal places). This one seems to work...

#,###.###,," MM"

 

[Gord Dibben]

My Excel version 2003 returns .345 MM with Custom Format I posted.

I tested before posting.

Yours returns same thing .345 MM



Gord

 

[Rick Rothstein]

Hmm! I wonder what the difference is? What SP are you at?

 

[Gord Dibben]

SP3


Gord

 

[Rick Rothstein]

I don't get it... I cannot make your suggest format pattern work on my
system... it keeps throwing away the comma leaving four # characters after
the decimal point.

 

[Gord Dibben]

I now see what you mean.

Enter in Custom .#,###,," MM" and OK

Excel changes to .####,," MM"

I just noticed that.

I never paid attention to what Excel did after I got the display in the
Sample dialog and hit OK

But the resultant display is .345 MM as OP wanted.

Sorry about not picking up the "missing comma" earlier.


Gord

 

[Rick Rothstein]

Phew! I'm thought I was going crazy there for a moment.<g>

Yes, you get the display the OP wanted... for that number. The original
intention of your format pattern was to round to 3 decimal places maximum
and to include the thousands separator... after Excel changes the pattern,
numbers will be rounded to 4 decimal places maximum and there will be no
thousands separator in numbers large enough to warrant them.