How to format a number with a different decimal separator as set in the locale setting

[Pulsar07]

In LO Calc, I want to format a number with a dot (".") as decimal separator. My language settings / local settings are and should be German, what forces the comma (",") as separator.
I guess the usage of the function TEXT() is what is needed in conjunction the the NatNum syntax.
But to be honest, I don’t understand how to use it and try and error does not succeed.
I only want to format a floating point number (e.g.: 1,1234) to be displayed/formatted as 1.234, without changing my different global locale settings.

Regards
Rainer

[Lupp]

The decimal separator is chosen depending on the settting under Language in the tab Numbers of the cell formatting dialog.
Your ‘Standard’ template for Calc documents contains the respective setting used by the default CellStyle.
[Edit 2021-04-08 about 09:50 GMT]
Concerning the usage of the TEXT() function I attach an example:
decimalSeparatorAndCoordinateSeparator.ods]
Side-effects of “l10n” are a nightmare to me. Therefore I use UI “en-GB” and locale “en-GB”. “Esperanto” is the ‘Numbers’ locale set for my default CellStyle in Calc.
If a formatting by TEXT() needs to accept an unwanted separator or even the silly thousands grouping by deprecated characters (what the comma and the point both are!), the SUBSTITUTE() function can be used to do repairs.
[/Edit]

[Pulsar07]

Yes, this works, but when preparing my GPS Coordinate output with the TEXT() formula (=TEXT(J4;"##,0000")&","&TEXT(L4;"##,0000")), it does not help when the source cell is formatted as suggestet. So another solution is needed.

[Lupp]

Your formula using the TEXT() function works for me as expected. The comma is used as the decimal separator. I misinterpreted an experience. The comma was taken as the idiotic “thousands separator”.
t’s a bad joke anyway to use the comma at the same time as the decimal separator and the coordinate separator.
I’m all but a expert, and just an occasional user of GPS data. Anyway: From my experience it might be preferrable to use the point as the decimal separator in this case, and to leave the misused comma in its place. This will ease global exchange. (And GPS is a global thing basically.)
Even the Germany-based software I (German, resident of Germany) sometimes used, only supported the English-based notation.
=TEXT(J4;"##.0000")&","&TEXT(L4;"##.0000") also works for me in the “German sheet”.

[Lupp]

In a second atttempt it worked. Don’t know the exact difference.
I attached a sheet to the answer. It is set to ‘Numbers’ language “German (Germany)”

[Pulsar07]

Using your attached sheet, shows “3,1416;2,7183”, what is exactly what I do not want
So it seems to be the only solution (as you suggested) to use the SUBSTITUTE function:
=SUBSTITUTE(TEXT(J3;"##,0000"); “,”; “.”)&","&SUBSTITUTE(TEXT(L3;"##,0000"); “,”; “.”)
It is horrible ;-((

[Lupp]

f you don’t experience more horrible things every day, you don’t use office software much.
Congratulations
BTW: Why do you need NatNum? It’s a nightmareto me. It will help to soon reach a state where we no longer can exchange simple numeric data without asking uncle Google to interpret them for us.

(Sorry! I had inadvertently edited your comment in advance, but hopefully there weren’t left traces.)

[Lupp]

Quoting @Pulsar07: “…what is exactly what I do not want ;-)”

I didn’t change my settings to match yours. You may have missed that my locale is en-GB, but the relevant cell was set to the “Numbers-language” de-DE.
As I see it, this demonstrates that the setting for the cell overrides the application setting for TEXT() - and that’s what you probably need. Set the cells where you want to get the result to a “Numbers-language” using the point as its decimal separator. Done! (Hopefully?)

Generally I recommend to globally use an English UI and locale. This way you ease global cooperation (like in this Q&A site e.g.) everything else concerning the doubtable l10n cosmos should be delegated to document settings where possible at all.

I don’t expect, of course, this advice would be accepted by a man usually using Bangla and wanting his numbers to be NatNum formatted. The Babylon mess isn’t cleaned yet. Instead we are busy enforcing it.

[Lupp]

See also: How to find how "implementation-defined" things **should** work?