8

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?

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

2 Answers 2

6

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.

3
  • 1
    Since today this seems not to work anymore Jan 25, 2019 at 7:53
  • And how / where does one apply the function? .. and is it just a one of or permanent "execution on change"? I just imported a CSV file with bank statements and need gsuite to see them as numbers instead of strings. search replace "." with "#" search replace "," with "." search replace "#" with "," In selection
    – GerardJP
    Jun 5, 2020 at 11:35
  • Joined community just to say that inside the formula we need semicolon instead of comma! Mar 17, 2021 at 22:57
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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