Number format as millions

NJS1982

Board Regular
Joined
Sep 24, 2009
Messages
179
Hi, does anyone know how to display numbers as "£0.55m"?
I have the code type for displaying anything OVER £1million
£#.##,,"m"
...but whenever a figure is below £1m, it shows as £.55m and I want it to show as "£0.55m"
Thanks!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,909
Office Version
365
Platform
Windows
Replace the first # with a 0
 

KirnonBhale

New Member
Joined
Jul 25, 2019
Messages
11
If you use £0.00 I think £0.0#,,"m" looks pretty nice.

Replacing just the first # with 0 will always leave a trailing "." after a round 1million. Which is why I replace the 2nd hash as well.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,909
Office Version
365
Platform
Windows
Glad we could help & thanks for the feedback
 

Forum statistics

Threads
1,085,931
Messages
5,386,824
Members
402,020
Latest member
tsfan74

Some videos you may like

This Week's Hot Topics

Top