How to Convert a Currency String into a Float Type


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.

For example:

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.

Step 1

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).

€ 1.000,00
1.000,00 €

First remove the currency smybol and the thousand separator. 

=REPLACEALL(#TextInput; "(\\€|.)")

This will result in the following string


In some cases you may remove also the decimal separator and trailing zeroes (,0).

=REPLACEALL(#TextInput; "(\\€|.|\\,0*$)")

The result will be


Step 2

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; "."; ""); ","; "."))