Calc US Dollar Currency Formatted Cells Do Not Align to The Right of The Cell, against The Cell Border

[Woof4Grrrr]

I am seeing unwanted blank spaces to the right of currency values.

I have a column of cells for which I have set a Number Format -
Category: “Currency”
Format: “USD $ English (USA)”
Sub-Format: “-$1,234.00”

The Format Code is “[$$-409]#,##0.00;-[$$-409]#,##0.00”

The Language is “Default - English (USA)”

The defaults of 2 Decimal places and 1 Leading zeroes are set
“Thousands separator” is selected
“Negative numbers red” is not selected

I have the Cell Horizontal Alignment set to Right.

I have Right border padding set to 0.0pt

And yet there is still a large gap between the last digit of the value and the cell border

How can I get rid of this useless space?

Thanks,
—Mark

[Villeroy]

You imported the figures from csv and forgot to check “detect special numbers”

[EarnestAl]

If you have set Format Code is “[$$-409]#,##0.00;-[$$-409]#,##0.00” then the horizontal alignment is already done, so why did you set it? Probably because the “numbers” were left aligned. If they were left-aligned then you don’t have numbers in your spreadsheet, only text. If View > Value Highlighting is on (highlighted) then numbers are blue and text is black.
.
See How to convert number text to numeric data
.
In the attached spreadsheet, select the supposed dollar values and click Data > Text to Columns or try the other methods shown in the wiki
RandomPositive&NegativeDollarsText.ods (12.9 KB)

[Woof4Grrrr]

Thank you for your reply.


I didn’t actually enter that Format Code, It was placed there when I selected the format from the list. I don’t even know exactly what it does.


I set the horizontal alignment to RIGHT because I was trying to get rid of the space on the right. It didn’t work. I removed the border padding for the same reason. I tried several different things to fix the problem. None of them worked.


The numbers were never left-aligned because I set the Number Format to the Currency Format before I entered any data into them. And I am entering numbers into the cells. They are being formatted correctly as currency. Just with the extra empty space to the right.


I do sometimes enter non-numeric data in a cell or two, for example “x” or “FREE”, to signify there was no money involved at this time. These are also right-aligned with the currency values and with the same extra space to the right. (Actually, I think these values are why I originally set alignment to RIGHT.)


If I make the column narrower such that it’s too narrow to display some of the currency values, the value is replaced with ### as expected, but still with the empty space to the right.


BUT, as an experiment, I just set the horizontal alignment to DEFAULT and now all the currency (decimal) values are correctly aligned - no empty space on the right - but the non-numeric values are all aligned to the LEFT. I want everything right-aligned.


This is a spreadsheet that I use as a monthly “template”. I start with a copy of this template each month and fill in the values. I never know which of these cells will have the non-numeric values, so I cannot just set individual cells in template to be right-aligned.


As a last test, I removed all direct formatting from the cells, then set the Currency Format again, then set right-alignment (for the non-currency values,) and now everything looks fine. I don’t know what other setting might have lead to the problem.


So I’m good now.


Could this have been some bug? I don’t know.

[EarnestAl]

In Calc it is possible to set an indent for left aligned text, but not for right aligned.
Excel allows indents for both right and left. The formatting doesn’t define which currency the dollars are so I should have spotted that the formatting code came from Excel.

.
The way to fix the Excel formatting would be to select the column and then double click the Excel style in the sidebar. For some reason, this removes the the indent better than other methods.Other cell styles or formatting can be applied afterwards.

Alternatives to remove the indent are

1. Select the cells (not column as it doesn’t work) and double click Default in cell styles to apply Default style and then format the cells as currency.

2. Create a new cell style for currency defined in Calc and apply that style to the selected cells by double clicking them

   1. Right click Default cell and select New.
   2. In the dialogue give the cell style a name, e.g. US Dollars
   3. In the Numbers tab, select Currency, format of USD $ English (USA) and select a dollar style, e.g. [$$-409]#,##0.00;-[$$-409]#,##0.00. You could set it right aligned if you are going to have text in the column.
   4. Select the cells and double click US Dollars in cell styles to apply the style

Here is sample I created for testing
IndentedValues.xlsx (8.2 KB)