Find and Replace in Google Sheet Macro-CodePudding

I have a Google sheet file where each month I need to copy some data, and they are copied like that: "-1 200,00 EUR" OR "= 80,66 EUR" I would like to create a macro that automatically replaces:

- "," with "."
- " EUR" with ""
- " " (shortspace) with ""

The problem is that the value = 80,66 EUR appears like a formula and so as #ERROR and when I try to replace it, it doesn't work. This is the script I wrote:

function ReplaceH() {
  var spreadsheet = SpreadsheetApp.getActive();
  var range = spreadsheet.getRange("D1:E49");
  var data  = range.getValues();

  for (var row = 0; row < data.length; row  ) {
    for (var col = 0; col < data[row].length; col  ) {
      data[row][col] = (data[row][col]).toString().replace('= ', '');
      data[row][col] = (data[row][col]).toString().replace(' ', '');
      data[row][col] = (data[row][col]).toString().replace('EUR', '');
      data[row][col] = (data[row][col]).toString().replace(',', '.');
    }
  }
  range.setValues(data);
};

CodePudding user response：

Try this

=SUBSTITUTE(regexextract(A1,"[\-0-9, ] ")," ","")

CodePudding user response：

Inside a script, use

function ReplaceH() {
  var spreadsheet = SpreadsheetApp.getActive();
  var range = spreadsheet.getRange("D1:E3");
  var data  = range.getValues();

  var regExp = new RegExp("[\-0-9, ] ");

  for (var row = 0; row < data.length; row  ) {
    for (var col = 0; col < data[row].length; col  ) {
      var x = regExp.exec(data[row][col]);
      data[row][col] = x[0].replace(/ /g,'')
    }
  }
  range.setValues(data);
};