Need a Number Format

[Montrey]

Hello I need a number format that will:

1,500,000 --> 1.5M

150,000 ---> 150K

1500 ---> 1.5K

-1500 --> -1.5k or (1.5K) Whichever is possible

-150,000 --> -150K or (150K) Whichever is possible


I have this:

[>=1000000]0.0,,"M";[>=1000]0,"K";0


But it doesn't format the Negative numbers correctly. Please help

or maybe there is a work around?

 

[SirJB7]

Hi, Montrey!

Couldn't get it working neither. Give a look at this, maybe you find something suitable for this case: http://chandoo.org/wp/tag/custom-cell-formatting/

As far as I could see, it refuses to handle more than three conditions in number format, so you'll have to use VBA with Worksheet_Change event, supervise proper range and apply formats by code.

Regards!

 

[Montrey]

I wonder if anyone of you wonderful people of the Chandoo boards could post VBA code for my first post!


if it is a fixed range. of only 5-10 cells

 

[NARAYANK991]

Hi ,


The following link has the code :


http://www.mrexcel.com/forum/showthread.php?t=373449


Narayan

 

[Montrey]

Thanks Narayank! that worked.

 

[Kyle McGhee]

Montrey,


You could try this as well, if the numbers are for presentation purposes as it creates a text number that cannot be used in calculations (without further formulas)


=IF(ABS(A1)>=1000000,TEXT(A1,"0.0,,M"),IF(ABS(A1)>=1000,TEXT(A1,"0.0,K")))


150K is formatted as 150.0K, not exactly how you wanted but close