We will be migrating from Ask to Discourse on the first week of August, read the details here

# Decimal Separator Change

Hello,

our ERP system exports currency references with dot values, on which can't be used SUM function - returns 0. Even if i change it from Windows local settings to be dot instead comma Libre Office still can't SUM values.

The language settings for Windows and Libre are Bulgarian(and must stay that way), the "Same as local settings" check in Libre seems not to do anything at all. The only way i found is if Locale Setting is set to USA, Default currency is Default - BGN and Date acceptance patterns are copy paste from Bulgarian, but changing 3 fields for 300+ PCs is really not an option.

Is someone faced this issue ?

edit retag close merge delete

Sounds like your values end up as text content in cells, not numbers. How are the textual values imported to Calc? CSV file?

( 2018-11-16 00:28:46 +0200 )edit

The values are copy/paste from the ERP, but if i open new sheet and format the cells to numbers manually and put numbers manually still SUM returns 0.

( 2018-11-16 07:34:19 +0200 )edit

Sort by » oldest newest most voted

Applying a number format to a cell does not change the cell's content or its type, i.e. text stays text. Either pre-format the cells with a number format of a locale that has the desired . dot decimal separator (e.g. en-US) before pasting the clipboard content, or for how to convert existing text to numbers see this FAQ.

more

This decimal separator issue makes the whole thing worthless. (for me) I cannot re-train my users to do this.

After about 3 months of suffering I'm moving them all over to Google Sheets.

Funny. the region settings on all the computers I tested it on was fine. I've used openOffice before Libre, and cannot think I had the same problem there?

more

Did you intend a comment on the answer by erAck?
What is your actual issue?
If you don't change your attitude, I'm afraid you won't get anywhere.

( 2020-04-06 01:57:59 +0200 )edit
2

## You have given an answer

Your posting an "answer" is formally a proposed solution to the issue, as posted by @Infernalz more than one year ago. I suspect that this is not what you intended.

## For what it's worth

Google sheets does the same thing for text content with the SUM() function as Calc does. It behaves differently (in my experience slightly less predictable) when adding by + operator. It may be more reliable with respect to correctly setting locale, I don't know.

If your users are not aware of the fact that half the world uses period character as decimal separator and the other half uses comma, and/or cannot handle deviant input, they should not be trusted to do any assessments involving arithmetic where source data may originate outside your organization, using spreadsheet software or other tools. Any worker (and student) should know the peculiarities and limitations ...(more)

( 2020-04-06 02:12:27 +0200 )edit

## Stats

Asked: 2018-11-15 21:12:39 +0200

Seen: 5,226 times

Last updated: Apr 06 '20