Parsing a STRING type to a FLOAT and being able to detect a comma or a dot as decimal separator.
Depending on the localization of the different currency symbols, thousand and decimal separators are used.
In German, the decimal separator is a comma (,), in English it is a dot (.). In Europe the currency symbol is mostly €, in the US it is $.
The following approach will work for different localizations, whereby it will be necessary to adjust the currency symbol and separators.
If your currency string is for a Euro amount, it will include the € symbol, a dot (.) as thousand separator and a decimal ending with comma (,00).
First remove the currency smybol and the thousand separator.
This will result in the following string
In some cases you may remove also the decimal separator and trailing zeroes (,0).
The result will be
If necessary, correct the decimal separator
=SUBSTITUTEALL(#TextInput; ","; ".")
Which will return
and convert it to FLOAT.
If your string does not have a currency symbol, you may directly use
=FLOAT(SUBSTITUTEALL(SUBSTITUTEALL(#TextInput; "."; ""); ","; "."))
Please sign in to leave a comment.