Convert number to Million Currency format

Y

Yossy

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

Gord Dibben

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

That's dot#comma###commacomma


Gord Dibben MS Excel MVP
 
R

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"
 
G

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
 
R

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.
 
G

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
 
R

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top