How to Convert a Currency String into a Float Type

Goal

Parsing a STRING type to a FLOAT and being able to detect a comma or a dot as decimal separator.

Intension

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

Learn

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

1000,00

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

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

The result will be

1000

Step 2

If necessary, correct the decimal separator

=SUBSTITUTEALL(#TextInput; ","; ".")

Which will return

1000.00

and convert it to FLOAT.

If your string does not have a currency symbol, you may directly use

=FLOAT(SUBSTITUTEALL(SUBSTITUTEALL(#TextInput; "."; ""); ","; "."))