Removing a row containing a specific text in Google Sheets

1
votes

I have a data set of around 3000 columns, but some of the columns have several cells that contain cells "na". These rows have no importance since they don't have data that I will need, is there a command in google sheets that can either highlight the entire row that contains that text or delete the entire row containing that text?

Any help would be appreciated.

Example of columns that have <code>na</code> in cells, the row must be deleted

https://docs.google.com/spreadsheets/d/1u8OUfQOzgAulf1a8bzQ8SB5sb5Uvb1I4amF5sdGEBlc/edit?usp=sharing

My document ^.

3
regexgoogle-apps-scriptgoogle-sheetsgoogle-sheets-formulags-conditional-formatting
You can use filter the data and copy paste in new sheet. hope you understand my point . – hardy
How many rows of data do you have? 3,000 columns is a fair bit, just for single row. The match function would work, but recalculation time could be an issue. If you are going to delete the row anyway, I'd opt for a script that detects "na" then deletes the row – Tedinoz

3 Answers

2
votes

you can use this formula to color all na rows:

=ARRAYFORMULA(REGEXMATCH(TRANSPOSE(QUERY(TRANSPOSE($A1:$Z),,999^99)), " na "))

0

1
votes

This answer based on what I understand, sorry if I'm wrong. You can use conditional formatting to highlight all NA text enter image description here

This is what rules I used

enter image description here

Here are another answers that may help you

  1. Delete a row in Google Spreadsheets if value of cell in said row is 0 or blank

  2. Google Sheets: delete rows containing specified data

  3. Deleting Cells in Google Sheets without removing a whole row

Sorry for bad English.

1
votes

I'm not sure if my understing is well but see below what you can do. This is a google script function which color the whole column where "na" is in

  function myFunction() {
//get the spreadsheet where the function is running
  var ss = SpreadsheetApp.getActive()
  //Replace "the name of your sheet" by your sheet name" be careful its case sensitive.
  var sheet = ss.getSheetByName("The name of your sheet")
  //Get all your data as an array (If your sheet has no header, change 2 by 1 and (sheet.getLastRow()-1) by sheet.getLastRow())
  var values = sheet.getRange(2,1,(sheet.getLastRow()-1), sheet.getLastColumn()).getValues();

  //For each column
  for (var i = 0; i< sheet.getLastColumn(); i++){
  //using function map is helping to select one column by one column
    var mapValues = values.map(function(r){return r[i]});
    //Searching your keyword in the column, in your case it's "na"
    var position = mapValues.indexOf("Put the string that you are looking for, in your case 'na'");
    //if at least there is one "na" inside the column
    if( position >-1){
    //then this color have to get red color as a background
       var wholeColumn = sheet.getRange(2,(i+1),(sheet.getLastRow()-1));
       wholeColumn.setBackground("red");
    }
  }
}``

Let me know if it works