Parsing #,###.## vs. #.###,## or whatever

General
1

I’m looking for a good way to parse numbers entered into a listbox cell, converting them into doubles. The numbers can be in either American or European format (so the decimal point and thousands separators are different). In most cases I do know which system is being used, but my mind has wandered off today and it suddenly seems complicated to get this text back to a double.

Thoughts?

2

The most difficult seems to be to remove the thousands separators, after which you want to turn the European comma into a dot, and your are set to use CDbl.

3

So if I know it’s european format, a simple ReplaceAll(".", “”) followed by ReplaceAll(",", “.”) should do it, right? Assuming that a period is the thousands separator.

I just realized the easy way to find out the replacements is to make a string using FORMAT with thousands and see what Xojo uses, then do the same thing with a decimal.

Are there any other tricky things I should know about, or is it really that straightforward?

4

[quote=285656:@John McKernon]I’m looking for a good way to parse numbers entered into a listbox cell, converting them into doubles. The numbers can be in either American or European format (so the decimal point and thousands separators are different). In most cases I do know which system is being used, but my mind has wandered off today and it suddenly seems complicated to get this text back to a double.

Thoughts?[/quote]
Does CDbl not work?

5

In general (France, Germany, Spain, Italy… ), the thousands separator is coma. BUT in Switzerland it is single quote. There may be other local peculiarities.

See Decimal separator - Wikipedia

I would tend to remove anything that is not digit, negative or dot.

Not with comma decimal separator, no. In the case of 123.456,56 as we would write in Europe, Cdbl will do 123.4565599999999961

6

Ah, that explains what I’m seeing. I’ll ask my user for a screen shot to confirm.

Thanks!

7

off the top of my head

FUNCTION cleanNumber(s:String) as String
var ThouSep = replaceAll(format("9,999"),"9","") // should return comma for US and dot for Europe?
var DecSep = replaceAll(format("9.99"),"9","") // should return dot for US and comma for Europe?
s=replaceAll(replaceall(s,decsep,"."),,thousep,"")
// should remove thousand seps and change decimal indicator to dot
return s
END FUNCTION
8

That looks pretty much like what I was figuring I’d have to do, I’m putting together a test project to send my european beta testers and see if we’ve finally got it right.

Thanks!

  • John
9

CDBl should be right otherwise there’s a significant bug

http://documentation.xojo.com/index.php/CDbl

10

[quote=285674:@Norman Palardy]CDBl should be right otherwise there’s a significant bug

http://documentation.xojo.com/index.php/CDbl[/quote]

Cdbl does not seem to take into account local settings for decimal separators. Not sure it is a bug.

But now that I looked into it, new framework Double.FromText does take a locale. It should then interpret separators according to the system setting.

11

from the docs on cdbl
Returns the numeric equivalent of the passed string. This function is the same as the Val function but is international-savvy.

12

OK. This works.

So no need for replace and stuff. Unless of course the user generates comma separated decimals data on a different system setting.

13

[quote=285673:@John McKernon]That looks pretty much like what I was figuring I’d have to do, I’m putting together a test project to send my european beta testers and see if we’ve finally got it right.
[/quote]
NO Michel was wrong

Just use CDBL which behaves correctly
Failing that use Double.Fromtext with a specific locale
Either one should work

14

Sorry.