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

Ask Your Question
0

Have "locale settings" per document? [closed]

asked 2019-12-25 23:13:02 +0200

marcusman gravatar image

updated 2021-05-27 16:07:11 +0200

Alex Kemp gravatar image

Hello,

I'm having trouble with the decimal separator in Calc. I use this as part of a formula in Calc:

TEXT(10000,"[>0]+#,##0;[<0]-#,##0;General")

Usually it would convert the number to something like +10,000 in an English document. That's totally fine and okay. Now to the difficult part:

Sometimes there are users (and documents) that use German instead of English. In the German language, the , (comma) is the decimal separator, not the . (point). So when a user switches to German, this suddenly becomes the output: +10000,000

What I would like to have, is either one of these:

  • A way to ask Calc what separator is used (could return "." or ",").
  • A way to save the type of separator in the Calc file itself (settings would override user's locale settings)
  • A way to give a neutral format to the TEXT()-function that won't change.

Can any of these three be done with the current version of Calc? (Hopefully without scripting) If not - should I write a feature request for this?

Idea: The first thing might be done like this: Have the number 10000 be transformed to a string. Compare that string to +10,000 and +10000,000. Good idea?
EDIT:
Works. A little tedious, but I made it a variable GS (for "group separator") and now I write:

TEXT(10000,"[>0]+#"&GS&"##0;[<0]-#"&GS&"##0;General")

Did not solve all my problems but I'm a bit closer to the solution.

FYI: I recently decided to write a C# application to solve all problems. For quite a long time, I've been avoiding to code my own app for this. But I think what I'm doing with Calc is not what it was intended for. Basically I'm trying to make an automatic character sheet for a fantasy game.

Best regards,

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2021-05-27 16:07:41.740186

1 Answer

Sort by » oldest newest most voted
1

answered 2019-12-26 07:18:18 +0200

Just explicitly set the cell's number format language to English (USA) (not "Default - Whatever").

edit flag offensive delete link more

Comments

I know this feature. Doesn't work in this scenario.
The number is to be converted into a text, using a format (see my two code blocks).

marcusman gravatar imagemarcusman ( 2019-12-27 02:44:17 +0200 )edit

Before answering, I specifically checked that is works (I use Russian locale with , being decimal separator, like in German locale, so I get the "+10000,000" result by default; using en-US as cell format language does make your formula return "+10,000"). Using 6.4.0.1 here, but explicitly tested that it also works with 5.4.4.2. You might need to recalculate (F9) after changing the cell language.

Mike Kaganski gravatar imageMike Kaganski ( 2019-12-27 02:51:36 +0200 )edit

Ah yes. After "recalculate" it works. Interesting.
Thanks for the help!

marcusman gravatar imagemarcusman ( 2019-12-27 15:23:15 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2019-12-25 23:13:02 +0200

Seen: 430 times

Last updated: Dec 26 '19