Google Spreadsheet: format IMPORTXML value

Question

I want to format the value I'm getting from IMPORTXML in a Google Spreadsheet.

I am importing numbers from a table and some of them are written out like this:

5892.9

2001.44.00

Instead of this:

5892,9

2001,44

This causes a problem when I'm doing sums with the results since it's interpreted as text instead of a number. It does not work to simply format the cells.

How can I either do sums with these scraped numbers as is or format the retrieved data to an accepted numeric value with one decimal? Preferably the later.

Here's the import that scrapes the table:

=IMPORTXML(B58;"//*[@id='rrtable']/table/tbody/tr[3]")

And here's where it's scraping it from (B58): https://www.investing.com/equities/coresite-realty-corp-balance-sheet

EDIT 1: Well, for now I've made a solution with =SUBSTITUTE cleaning the numbers up in two different stages, first to replace "." with "," and then to replace ",00" with "". It's not pretty or compact, but it works I guess...Would still like to hear a more proper solution!

EDIT 1: Think I managed to figure out a more compact solution with wrapping the importxml in two different substitutes. Basically does the same thing as above "EDIT" but in the same cell.

Answer 1

I think your solution with SUBSTITUTE is good.

I would've done the same usging regex, but the idea is the same (A1 is what you get from IMPORXML):

=VALUE(REGEXREPLACE(A1; "^(\d+)(?:[.,](\d*).*)?"; "$1,$2\0"))