[Solved] Format("1.1", "0.0") gives 44562,0

[Jimmy2768]

Hi to all

While trying to format some string values to numbers using Format Subrutine i get strange result.
The code:

Code: Select all

Dim A as String
A = "1.1"

Dim B as String
B = Format(A, "0.0")

Print B

Shows the value of variable B = 44562,0

I tried on OpenOffice 3.2.0 and Apache OpenOffice 4.1.11. Same results.

On LibreOffice 7.0.6.2 runing the same code B = 11,0. Wrong too.

Meaby a bug?

[JeJe]

running your code gives me 1.1

[Jimmy2768]

Thank you for reply JeJe

I tried my code on OpenOffice, Apache OpenOffice, on LibreOffice, On Linux and Windows on different machines. Always wrong values.

I believe this is because the decimal point in my country set in operating system is "," and not "."

[Villeroy]

See Tools>Options>LanguageSettings>Languages: Locale (2nd Option).
It might be set to German or something similar.
Enter 1.1 into some spreadsheet and the result will be 01.01.2022 which is cell value 44562 formatted as date.

[Jimmy2768]

See Tools>Options>LanguageSettings>Languages: Locale (2nd Option).
It might be set to German or something similar.
Enter 1.1 into some spreadsheet and the result will be 01.01.2022 which is cell value 44562 formatted as date.

Thanks Villeroy
The Locale setting is correct. We have "," for decimal point in my country as in your's country.
The results of Format subrutine is different according the Locale setting?
It gives correct result on some countries and wrong results on another?

[JeJe]

what does Format(A, "0,0") give (using the comma)?

If that's 1,1 then its behaving properly.

Edit:

and you just need to change the dot to a comma.

Code: Select all

mid(A,instr(1,A,"."),1) =","

[Villeroy]

Meaby a bug?

IMHO, the programming language should not follow the user interface locale. So it may be seen as a bad design decision but not as a bug. If you imported wrong numbers with decimal points from clipboard or from text files into a sheet, just import them correctly and you don't need any macro.
If you have to fix the wrong data, just replace the points with commas.

[Jimmy2768]

what does Format(A, "0,0") give (using the comma)?

If that's 1,1 then its behaving properly.

Edit:

and you just need to change the dot to a comma.

Code: Select all

mid(A,instr(1,A,"."),1) =","

Yes using comma the result is correct.
So Format want's "," or "." according to locale setting in order to work.

[Jimmy2768]

Meaby a bug?

IMHO, the programming language should not follow the user interface locale.

I agree with you Villeroy.

[JeJe]

The Format function has to be locale aware because its dealing with dates and so on. OOBasic is pretty much a clone of Microsoft's Visual Basic and looking at the documentation for Format for that, that is locale aware for numbers too.

In OO's help file for Format it says:

Sub ExampleFormat
MsgBox Format(6328.2, "##,##0.00")
REM always use a period as decimal delimiter when you enter numbers in Basic source code.
REM displays for example 6,328.20 in English locale, 6.328,20 in German locale.
End Sub

You're entering a string though, not a number. I presume if instead of a string you declared A as a single or double it would give the result you want?

[Jimmy2768]

REM always use a period as decimal delimiter when you enter numbers in Basic source code.

You're entering a string though, not a number. I presume if instead of a string you declared A as a single or double it would give the result you want?

You're entering a string though, not a number. I presume if instead of a string you declared A as a single or double it would give the result you want?

Yes you are right. The help talks about numbers.
If i declared as Single or Double the result is correct.

In my case must be Strings, but i am' going to change dots with commas

[Jimmy2768]

Thank you both guys