6

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?

  • Are original values always three characters before and after the dot or comma? – Rubén Jun 10 '15 at 11:27
5

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.

  • 1
    Since today this seems not to work anymore – Richard Jan 25 at 7:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.