How to convert all numbers regardless of decimal separator into valid number of current locale?

Question

I have a column with text looking like this:

123,123
123.123
123,123
123.123

I would like another column to parse this into decimal numbers, disregarding the different decimal separators (dot . and comma ,) so the result would be like this when locale has dot as separator:

123.123
123.123
123.123
123.123

And like this when locale has comma as separator:

123,123
123,123
123,123
123,123

How can I do this in the simplest way possible in Google Sheets?

Answer 1

The following formula does the job (tested with U.S. and Russia as locales, where separators are different):

=IFERROR(VALUE(SUBSTITUTE(A2, ",", ".")), VALUE(SUBSTITUTE(A2, ".", ",")))

Explanation:

• IFERROR returns the first argument unless it throws an error, in which case it returns the second.
• The first argument of IFERROR is the result of converting the text, with any commas replaced by dots, into a number.
• The second argument of IFERROR is the result of converting the text, with any dots replaced by commas, into a number.

I'm assuming that in every locale, one of two things is going to work.

Answer 2

For all numbers

Whether they are using thoudsand and comma separators or not (e.g. 1,000,000.00 or 1.000.000,00)

=IFERROR(
    VALUE(A1),
    IFERROR(
        VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E1, ".", "~"), ",", "."), "~", ",")),
        VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E1, ",", "~"), ".", ","), "~", "."))
    )
)

In a single line:

=IFERROR(VALUE(A1),IFERROR(VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, ".", "~"), ",", "."), "~", ",")), VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, ",", "~"), ".", ","), "~", "."))))

As an ARRAYFORMULA:

=ARRAYFORMULA(IFERROR(VALUE(A1:A),IFERROR(VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1:A, ".", "~"), ",", "."), "~", ",")), VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1:A, ",", "~"), ".", ","), "~", ".")))))

For currencies

In a single line:

=IFERROR(VALUE(SUBSTITUTE(A1,"€","")),IFERROR(VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"€",""), ".", "~"), ",", "."), "~", ",")), VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"€",""), ",", "~"), ".", ","), "~", "."))))

As an ARRAYFORMULA:

=ARRAYFORMULA(IFERROR(VALUE(SUBSTITUTE(A1:A,"€","")),IFERROR(VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1:A,"€",""), ".", "~"), ",", "."), "~", ",")), VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1:A,"€",""), ",", "~"), ".", ","), "~", ".")))))