I want to align a column of cells so that they appears as follows:
$ 9.00
$ 10.00
$ 105.35
$ 3.33
Rather than the default of:
$9.00
$10.00
$105.35
$3.33For the English-US and some other locales there is already such a predefined number format, in the number formatter dialog pick the (most times last in list) currency format
[$$-409]* #,##0.00;-[$$-409]* #,##0.00
Important is the * (asterisk, space) part. For other locales use the default currency format and modify it by inserting * (asterisk, space) between […] symbol designator (the actual symbol, and 409 ID of course vary between locales) and number characters.
3 Likes
Could someone explain what the various parts of this format accomplish? In particular I don’t follow what [$$-409]* does. It sounds like [$$-409] tells it to use the dollar sign? Does the asterisk space indicate left justification of just that bit?
I ask, because I am having trouble with the decimal point being aligned. If there are enough digits to the left of the decimal, even though there is plenty of white space between the number and the dollar sign, the number is pushed to the right a bit so the decimal points don’t align.
The effect is a bit different in the spread sheet and the printed PDF. The PDF only shows the problem if there are five or more digits to the left of the decimal point. In the spread sheet it’s three or more.
The problem seems to go away if I make the column much wider, but then there are several characters of white space to the right of the dollar sign, wasting space.
According to Help, $$ is dollar and 409 is US; Canadian Dollar (English) would be [$$-1009]. Have a look in format cell and try out a few currencies to see the different codes.
It might the font that is the issue. Liberation Sans, Serif, and Arial line up nicely but if I use Comic Neue then the decimal points do not align
Could be a zoom level issue.
Check it in Print Preview (Ctrl+Shift+O).
See Excel accounting format makes underlines just short of the width of the column. Can LO do that? - #12 by LeroyG
1 Like
Thank you for the suggestion, but the problem is how it is being printed.
Yes, I had encountered this before with this sort of formatting and had forgotten that it will only work correctly with a mono-spaced font. I used Arial Monospaced to match the Arial font used elsewhere and that works acceptably.
I don’t really get what the various parts of the format designate exactly, but the left justification of the dollar sign is not correct when not using a mono-spaced font. The dollar signs create a jagged line along the left margin.