0

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.

1


  • 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")

  • 1
    I'm already using IMPORTRANGE already, as a workaround. The *1E-11 won't work as sometimes I get an integer. – Adriano P Feb 12 '19 at 19:10
  • 1
    you could wrap it in IF function to differentiate between states with and without *1E-11 – user0 Feb 12 '19 at 19:14
0

Use the xPath concat function to append a text character to the imported value to force Google Sheets to threat it as text. I.E. the following formula appends a _.

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

Then the _ character could be removed by using SUBSTITUTE or other methods. Example:

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

-1

I had a similar issue using IMPORTXML and I just changed to IMPORTHTML to resolve it.

  • 1
    Welcome. It's good to know that IMPORTHTML work for you but it will not work in all cases. Have you tried to use it for the case shared by the OP? – Rubén Jul 21 '19 at 15:07

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.