CODEWITHSUNDEEP
xmlgoogle-sheetsgoogle-sheets-formula
Blueboy90780
Blueboy90780

Reputation: 155

How do I change imported data from decimals to commas in Google Sheets?

Using the =IMPORTHTML function, I imported data from a website to google sheets. The page I want to import from is: https://www.tools4albion.com/refining.php?cat=ore&city=5&quantity=80&skillT4=28&skillT7=0&return=5&tax_sell=6&tax_shop=30&skillT5=17&skillT8=0&tax_order=1.5&skillT6=1, however the website uses decimals rather than commas to separate numbers (not to confuse with fractional numbers like "0.2" and so on). When viewed on Google Sheets, it creates 2 problems:

  1. The numbers with triple decimals gets automatically aligned to the left rather than the usual right.

Screenshot of Google Sheets with numbers misaligned to the left

  1. It leaves some very important data to indent, which completely changes it's value (the value is supposed to be 545760 but google sheets displays it as 545.##)

    enter image description here

    enter image description here

  2. No calculations, functions or arithmetic can be performed on triple-decimal data. Even those with decimal are treated as fractional numbers rather than integers.

    enter image description here

    enter image description here

For those of you who want to try it, I used the formula:

=IMPORTHTML("https://www.tools4albion.com/refining.php?cat=ore&city=5&quantity=80&skillT4=28&skillT7=0&return=5&tax_sell=6&tax_shop=30&skillT5=17&skillT8=0&tax_order=1.5&skillT6=1","table",5)

Any ideas on how I can fix this issue? If there is no such solution that exist on Google Sheets. I have no problem doing this with Microsoft Excel. Answers from either software is fine with me.

Upvotes: 1

Views: 434

Answers (1)

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(IF(REGEXMATCH(""&IMPORTHTML(
 "https://www.tools4albion.com/refining.php?cat=ore&city=5&quantity=80&skillT4=28&skillT7=0&return=5&tax_sell=6&tax_shop=30&skillT5=17&skillT8=0&tax_order=1.5&skillT6=1",
 "table", 5), "\d+\.\d+\.\d+$|\d+\.\d+$"), SUBSTITUTE(IMPORTHTML(
 "https://www.tools4albion.com/refining.php?cat=ore&city=5&quantity=80&skillT4=28&skillT7=0&return=5&tax_sell=6&tax_shop=30&skillT5=17&skillT8=0&tax_order=1.5&skillT6=1",
 "table", 5), ".", )*1, IMPORTHTML(
 "https://www.tools4albion.com/refining.php?cat=ore&city=5&quantity=80&skillT4=28&skillT7=0&return=5&tax_sell=6&tax_shop=30&skillT5=17&skillT8=0&tax_order=1.5&skillT6=1",
 "table", 5)))

Upvotes: 2

Related Questions