Original Title:

How to use Google Sheets formula to import data (using e.g. IMPORTXML) without dropping off characters in results?

Google Sheet example in thrid update below.

I currently use the formula below to pull the price of a cryptocurrency from the Coingecko website. The formula works fine with prices that are not too long. However, this cryptocurrency has a very low and hence long value. Long as in the number of characters.

E.g. the price currently is $0.000000000086801 (18 characters)

However using the IMPORTXML it shows $0.000000000087 (15 characters)

In other words, it's missing the three digits at the end of the price. I'm guessing this might be related to some limitation of displaying a maximum of 15 characters. Though I would've thought that's only applicable to number values — IMPORTXML is displaying the prices as text.

Is there a solution or workaround using IMPORTXML (or another formula/way) to enable Google Sheets to display the full/exact price as shown on the website without dropping off characters/digits?

Formula: =IMPORTXML("https://www.coingecko.com/en/coins/nekocoin", "//div[contains(@data-controller,'coins-information')]//span[contains(@data-coin-symbol,'nekos')]")

Update 1

The formula below manages to pull text longer than 15 characters (albeit including spaces) so not sure if this is an issue related to a character limit...

=IMPORTXML("https://www.coingecko.com/en/coins/bnb","//h2[@class='tw-text-lg px-3 md:tw-text-2xl tw-font-bold tw-text-gray-900 dark:tw-text-white dark:tw-text-opacity-87']")

Returns the result: "BNB Price Statistics" (20 characters including spaces)

However, the problems still stands when pulling the price.

Update 2

It's very possible to display a long number as a text. So if IMPORTXML is showing a text result by default, it shouldn't have a digit limit...

numerical cell value formatted as text has no digit limit

Update 3

Google Sheet below is editable. I've already added a suggested solution but no luck so far.

https://docs.google.com/spreadsheets/d/1KdXXGskUhFzH4NvRiCOA8ft_-Vl6NPjh1uGJzO15w4A/edit?usp=sharing

Update 4 Following brief discussion with @player0, it seems IMPORTXML was pulling a shorter price as it pulls data from non-javascript version of the webpage. This explains why it imported a text with character limitations.

Having checked this question here (ImportXML - Javascript? Imported content is empty) and this question here (Google Sheets importXML Returns Empty Value) it doesn't seem possible. Though there might be some sort of workaround? (What is the JavaScript equivalent of ImportXML?)?

It might be easier to just find a website that produces the price on the non-JS version of their webpage.

CodePudding user response：

try:

=REGEXEXTRACT(IMPORTXML("https://crypto.com/price/nekocoin", 
 "//h2/span"), "\d .\d ")

CodePudding user response：

parseFloat of string longer than 16 characters i think it is problems for 17 character itself on javascript, the library inherit this property too