Google Sheets - Delete rows with characters

user_10800851 0
Viewed 64

I am working with a sheet that has both numeric and text-values. What I would like to do, is to remove all the rows with text values and only keep the rows with numeric values. In this image, Column A represents what I currently have, Column B what I want to achieve. I have an example code that can be used to remove single values, but how could I make this work to apply for all the characters, not just specific words?

enter image description here

function deleteEmptyRows(){ 
  var sh = SpreadsheetApp.getActiveSheet();
  var data = sh.getDataRange().getValues();
  var targetData = new Array();
  for(n=0;n<data.length;++n){
    if(data[n].join().replace(/,/g,'')!=''){ targetData.push(data[n])};
    Logger.log(data[n].join().replace(/,/g,''))
  }
  sh.getDataRange().clear();
  sh.getRange(1,1,targetData.length,targetData[0].length).setValues(targetData);
}

Also, it doesn't need to be this solution that does the work. I am looking for the best solution. This is just an example I found.

google-apps-script google-sheets javascript regex
2 Answers

You said it doesn't need to be a solution that uses script? Then maybe you can use QUERY() with the matches() clause?

=QUERY(A1:A,"where A matches '\d+(?:-\d+)?'",0)

I'm not sure about the use of the current regular expression, but it does the correct matching for your currently shown sample data where:

  • \d+ - 1+ digits.
  • (?:-\d+)? - An optional non-capture group where a single hyphen is followed by 1+ digits.

enter image description here

user_97581940

For your example it could look like this:

function deleteEmptyRows(){ 
  var sh = SpreadsheetApp.getActiveSheet();
  var data = sh.getDataRange().getValues();
  var targetData = new Array();
  for(var n = 0; n < data.length; n++){
    var cell = data[n][0].toString().replace(/-/g,'') // remove dashes
    if (!isNaN(cell)) targetData.push(data[n]); // if the content looks like a number
  }
  sh.getDataRange().clear();
  sh.getRange(1,1,targetData.length,targetData[0].length).setValues(targetData);
}
user_142654690
Related