Custom Format - align currency symbols

[thesavage]

I am using the custom cell format to change the way large numbers are displayed but cannot find how to align the currency symbol similar to using the built-in "accounting format". I want to display "$97,234,567" (for example) as "$97.23 M" and align the subsequent cells with the currency symbol to the left of the cell. I have tried a few things and searched the net to no avail. Can any one offer assistance?

Cheers

 

[Sal Paradise]

The cell format for the accounting is:

_ $* [[number format]] _

You can remove the last _ which will move the number spacing a bit to the right of what the _ would give (the _ adds a space after the numbers for padding).

In the same way, the _ at the beginning adds a leading space, and the * repeats the space character to fill the gap between the currency symbol and the numbers.

What format are you trying to make exactly?

If you are planning on having some numbers end with "M" and some not, then it will be nigh impossible to line them up (you need to know the size and kerning of the various fonts to line them up all nice and proper, or use en and em spaces that result in a perfect match). But you can give it the old college try if you'd like.

 

[Rick Rothstein]

This Custom Format should do what you want...

_($* #,##0.00,," M"_);_($* (#,##0.00,," M")

If you will not have any negative values, then this should be all you need...

_($* #,##0.00,," M"_)

 

[thesavage]

Thanks Rick and Sal your quick reply and solutions - they work perfectly!

Cheers

 

[DetroitDavid]

This question was solved by user - no reply needed.

This was very helpful to me in tweaking a format on a large printed range for a business plan.

My question is this... "Is it possible to set the 'Style' of 'Currency' to show without the spacing on the right?", like:

This: $#,##0;[Red]($#,##0)

Rather than...
This: $#,##0_);[Red]($#,##0)

Thank you in advance...

DD

 

[DetroitDavid]

Although I found the answer to the above question, now I have a follow-up.

I successfully changed the default Currency style to this: $#,##0;[Red]($#,##0) (without the right-hand spacing and decimal).

But, since I may want that in the default style, I changed it back. Now, I created a custom style that is what I want and I want to add this to a new group on the ribbon, and possibly a keyboard shortcut like Ctrl+Shift+$

I see when customizing the ribbon, that it will accept a macro, but I don't know what to record which will both work and be eleganct enough not to go through a bunch of unnecessary steps like most recorded macros will.

Request:
Would someone be so kind as to show me or quickly write something which would work for me to add this as a macro as to make this style appear on the ribbon through customizing it into a new group?

Thank you !!!

-David