Google-sheets – IMPORTXML reading decimal separator as thousands separator

formulasgoogle sheetsimportxmllocalization

When I import this item via IMPORTXML and the spreadsheet locale is US (or other country that uses dot as decimal separator) it works fine:

=IMPORTXML("http://bvmf.bmfbovespa.com.br/cias-listadas/empresas-listadas/ResumoEventosCorporativos.aspx?codigoCvm=19348&tab=3.0&idioma=pt-br#fechar-modal", 
 "//*[@id='ctl00_contentPlaceHolderConteudo_grdBonificacao_ctl01_ctl12_lblPercOuFator']")

Result = 0.01

When I set the locale to Brazil, for example, it understands this dot as a thousand separator and imports the number as 1.000.000.000 (the source
number is 0.01000000000).

My problem is that I need to deal with both numbering format.

I have checked this question already, but the proposed solutions didn't work. Any ideas? So far I had to create a separated spreadsheet to deal with both formats, but it'd be good if IMPORTXML wasn't that clever trying to figure out what's the data format.

Best Answer

you can either correct it with *1E-11:

=IMPORTXML("http://bvmf.bmfbovespa.com.br/cias-listadas/empresas-listadas/ResumoEventosCorporativos.aspx?codigoCvm=19348&tab=3.0&idioma=pt-br#fechar-modal"; 
"//*[@id='ctl00_contentPlaceHolderConteudo_grdBonificacao_ctl01_ctl12_lblPercOuFator']")
*1E-11

or by using US sheet where you import it and then use IMPORTRANGE to import it in your Brasil sheet:
```
=IMPORTRANGE("1LBTbmztidw3517QHVqI-g3KAkiPp7qoCtOHhEtoc4iQ"; "Sheet1!A1")
```

Related Solutions

Google-sheets – ImportXML formula no longer working

Something went wrong on Google's side, apparently. Other stock symbols import correctly. My uneducated guess is that there was a transmission error and a corrupted fetch result got stuck in Google's cache. I was able to break the cache by adding a redundant URL parameter: http://www.bloomberg.com/quote/7501:JP?param=1 fetched correctly.

By the way, the selector @class='price-container down' will only fetch the price if it's going down, because otherwise the classes will be price-contained up. To fetch the data in either case, use contains(@class,'price-container') instead:

=ImportXML("http://www.bloomberg.com/quote/7501:JP?param=1", "//div[contains(@class,'price-container')]//div[@class='price']")

Google-sheets – Import table using IMPORTXML

You seem to have forgotten to mention what you would consider tidy, but it should be easier to handle your data (assumed to be starting in A1) with a formula such as:

=offset(A1,int(2*((row()-1)/2)),0)

in B1 and the same again in C1 with an extra +1 immediately before the ,0 - both copied down to suit.

OFFSET
INT
ROW

Best Answer

Related Solutions

Google-sheets – ImportXML formula no longer working

Google-sheets – Import table using IMPORTXML

Related Topic