So, i have been trying to build a google sheet so that i can track my crypto portfolio. i used =importxml to get the live data from coinmarketcap.com

This is the code, where E28 is the XPATH: =IMPORTXML("https://coinmarketcap.com/currencies/bitcoin/"; E28)

Its working perfectly since i added a button that when i click it, it refreshes the prices.

Here is the problem... The data that is pulled from coinmarketcap is displayed in the cells as text not as numbers, even thought it is in fact numbers. So when i try to use the data to make my profit / loss formula it says "Function ADD parameter 1 waits for numbers, but "$40,488.71" is actually text and it can't be forced as number"

What can i do? is it possible to turn this text into numbers? so i can use the values in my formulas?

Did i made my self clear? Regards from Brazil!!!

CodePudding user response：

Try

=Value(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IMPORTXML("https://coinmarketcap.com/currencies/bitcoin/";"//div[@class='priceValue ']");"$";"");",";"");".";","))

as you are in Brazil, you have to remove $, remove , and change . by , as decimal separator.