Convert text to number in Google Sheets

user_9785014 0
Viewed 8581

I'm reading currency values from a website and I'd like to do some calculations with these numbers. They come in this format: $7,821.24

Here's an example file: https://docs.google.com/spreadsheets/d/1vHEH_m16KXcDh7hY_BVG9lur1huFjWOnx5bWtgrdGdA/edit?usp=sharing

Now for some reason neither VALUE() and TO_PURE_NUMBER work for me (Can't parse to numberic value, telling me it's a text value). My guess is that the comma and the $-sign confuse the formula, is there anything I can do to format this correctly? The dollar sign always appears in the values but the comma only appears separating thousands of values.

I just started using Sheets for this so I absolutely have no clue. Would really appreciate if someone could help me out. Thanks for your time!

google-sheets-formula google-sheets-api google-sheets
2 Answers

Issue:

It's a locale problem. The value coming from IMPORTXML is formatted as in United States locale, so your spreadsheet (which uses a different format) cannot convert it.

Solution #1. Changing locale:

If the spreadsheet locale is changed to United States or others with the same format (via selecting File > Spreadsheet settings and setting the mentioned locale), the retrieved value will be a number, and you can work with it without using any other formula.

Solution #2. Formula:

If changing the locale is not an option, one possible way to convert the value to a valid number in your locale is the following formula:

=SUBSTITUTE(SUBSTITUTE(RIGHT(A2;LEN(A2)-1);",";"");".";",")
user_106120110

Changing locale didn't work in my case. But I was able to split the number which google sheet isn't detecting as a real number. So split it with "," and "." and any currency sign you may have, and then combine individual values for desired output

user_18165410
Related