I’m working on a spreadsheet to monitor my cryptocurrency investments and I’m using the =importxml function to pull live price data from a crypto website.
The function works great and pulls the data I need. However, I’m running into an issue where the imported values show up as text instead of numbers, even though they look like numerical values.
When I try to use these values in calculations for profit and loss, I get an error message saying something like “Function SUM parameter 1 expects numbers, but ‘$2,345.89’ is text and cannot be converted to a number.”
Is there a way to convert these text values into actual numbers so I can use them in my mathematical formulas? Any help would be appreciated!
The text conversion issue happens because importxml brings in formatted strings with currency symbols and commas. I’ve hit this exact problem building automated trading dashboards.
Wrap your importxml with VALUE and SUBSTITUTE functions to strip the formatting:
But managing crypto data in Google Sheets gets messy fast with multiple coins, refresh rates, and API limits. You’ll constantly fight formatting issues and manual updates.
I automated this whole process using Latenode instead. It pulls crypto prices from multiple APIs, cleans the data automatically, and pushes clean numbers directly to your sheets. No more text conversion headaches, plus you can add alerts when prices hit your targets.
The automation runs every few minutes and handles all the data formatting behind the scenes. Way cleaner than fighting with spreadsheet functions.
had the same problem a while back! using VALUE is key, like =VALUE(IMPORTXML(...)). if the $ sign’s messing things up, try REGEXREPLACE to get rid of the weird characters first. sheets can be picky with formats.
Yeah, those formatting tricks work but you’re missing the bigger picture. I wasted months on the same conversion headaches building portfolio trackers.
Here’s the thing - crypto sites constantly change their data structure. Your XPATH works today, breaks next week when they redesign. Plus Google Sheets has refresh limits and import quotas that’ll bite you.
I ditched all that and automated the whole pipeline instead. Built flows that pull from multiple crypto APIs, auto-normalize number formats, and push clean data straight to your sheets. No more tweaking formulas every time sites change their layout.
The automation handles currency conversion, decimal formatting, even fills gaps when APIs crash. Your spreadsheet just gets reliable numbers without the import mess.
Way better than fighting with importxml screen scraping. Bonus: you can add price alerts and auto-rebalancing.
Been tracking crypto prices for over two years and hit this same problem countless times. It gets worse with international sites using different number formats or when prices jump between magnitudes. NUMBERVALUE works way better than VALUE for me. It handles formatting variations automatically without manually stripping characters. Try =NUMBERVALUE(IMPORTXML("https://example-crypto-site.com/coins/ethereum/", F15)) first. If that doesn’t work, you’re probably dealing with invisible characters or encoding issues from the source. I’ve seen crypto sites return data with non-breaking spaces that look fine but break conversion functions. Wrap everything in TRIM and CLEAN before converting - usually fixes it. Watch out though - some crypto sites detect automated requests and start serving cached or weird formatted data after a while. If your imports suddenly break, that’s probably why.
This happens all the time with imported data. Google Sheets defaults to treating anything with currency symbols or special formatting as text. I’ve wrestled with this tons when pulling financial data for my tracking sheets. I usually combine SUBSTITUTE with VALUE to clean the data first. But there’s a better way - REGEXREPLACE handles multiple formatting problems at once: =VALUE(REGEXREPLACE(IMPORTXML("https://example-crypto-site.com/coins/ethereum/", F15), "[^0-9.]", "")). This nukes everything except numbers and decimal points, then converts to a real number. Way better than chaining multiple SUBSTITUTE functions since it won’t break if the website throws in random characters. Just watch out - crypto sites love changing their formatting randomly and breaking your imports. Found that out the hard way when a site started adding percentage symbols to price data.