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:


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 (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.


3 Answers 3


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(),'_')"),"_","")

  • How to use concat when importxml returns an array? I put concat in my xpath query but only the first row of the imported data shows up.
    – Ivan
    Dec 27, 2020 at 17:22

  • 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, 2019 at 19:10
  • 1
    you could wrap it in IF function to differentiate between states with and without *1E-11
    – user0
    Feb 12, 2019 at 19:14

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? Jul 21, 2019 at 15:07

Your Answer

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

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