IMPORTXML Change the imported format in a cell

Question

I used the IMPORTXML formula to import values from a website.

The formula imports a list of 4 cells in a column.

The value of the 3rd cell is a number with a dot, so it is recognized as text.

I would like to change the dot by a comma but when I use the SUBSTITUTE function, the value of the first cell is converted and the three other cells are empty.

Here is the basic formula:

=IMPORTXML("https://opensea.io/collection/primeapeplanetpap?tab=activity";"//div[contains(@tabindex,'-1')]")

Here is the formula with the SUBSTITUTE function:

=SUBSTITUTE(SUBSTITUTE(IMPORTXML("https://opensea.io/collection/primeapeplanetpap?tab=activity";"//div[contains(@tabindex,'-1')]"); "K";"");".";",")*1

I'm sharing a sheet

What I would like if possible is to change the format of the third cell only and leave the others as is.

If there is a solution with App script I'm interested too.

Thanks in advance for your answers

Answer 1

I don't know how it could be done with formula.

Here is the simply script that makes the changes in all the cells of the range:

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange('C2:C5'); // <-------- set your range here
  var values = range.getDisplayValues();

  for (let row in values) for (let cell in values[row]) {
    values[row][cell] = values[row][cell].replace('K','').replace('.',',')
  }
  
  range.setValues(values);
}

You can add the custom menu 'Script > Clean numbers' to run the function from the menu:

function onOpen() {
  SpreadsheetApp.getUi().createMenu('Scripts')
  .addItem('Clean numbers', 'myFunction')
  .addToUi();
}

---

I just opened your spreadsheet and it shows me correct numbers without replacing:

Probably the actual answer is not replace the values but change regional settings of the Spreadsheet to fit setting of the xml source?

Also you can try convert all the numbers into text before you grab them from the sheet. It can be done with one line right after var range = ...:

range.setNumberFormat('@');

And perhaps at the end after range.setValues(values):

range.setNumberFormat('#,##0.0000');

If you need numbers after all.