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.