Re: [office-formula] VALUE - what to do with "." and ","?

["David A. Wheeler" <dwheeler@dwheeler.com>]

I said:
> > The VALUE function takes a string and returns a number.  If it gets "," or "." in a regular number, what should it do?

Eike:
> Locale-dependent.

I asked: 
> > What do current implementations, including Excel, OOo, Gnumeric, KSpread, etc., do?

Eike:
> Gnumeric, Excel and OOo do it locale-dependent with no fallback to C or whatsoever.

Okay, I guess that's the answer.

> > Even if you don't like _automatic_ conversion of string to number, I think we all agree that there MUST be a function that does it... and that's VALUE.  But I don't think we can leave such an important issue unstated.
... 
> Since VALUE is completely locale-dependent, its regexp in the semantics
> section and the comment are wrong. The "leading '$' is ignored" applies
> only to the en-US locale (and others that have a '$' currency symbol).
> A leading '$' in a de-DE locale will result in an error. The separators
> parsed of course are those of the locale. Note that a fully locale-aware
> implementation may also parse other sign characters than just ASCII +/-
> 
> I suggest to completely remove that regular expression, it only causes
> confusion. Or have it as an _example only_ for an en-US locale.

I think we should instead REQUIRE support for that regex, but ONLY while in the en_US locale.  That way, we can have automated tests for VALUE actually work.  But you're quite right, if this is locale-dependent, it needs to SPECIFICALLY say that it's locale-dependent.

Can we at least REQUIRE that VALUE support integers, with optional prefixes + and -, in all locales?  I suspect THEY are valid in all locales...!

It's depressing that we have no function that's locale-INDEPENDENT, though, to do this job.  Many had concerns with VALUEL because handling a long list of locales was problematic.   But perhaps something like NUMBERVALUE(valuetext ; decimal_separator) would be okay? I'm thinking decimal_separator would be "," or ".".  Otherwise, pretty much all chars other than digits, leading +/-, and the decimal char would be ignored, and (num) would be accepted for negative numbers.

--- David A. Wheeler