Removing a row containing a specific text in Google Sheets

Question

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.

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

My document ^.

You can use filter the data and copy paste in new sheet. hope you understand my point . — hardy, Oct 30 '19 at 12:34
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, Oct 30 '19 at 12:52

score 3 · Accepted Answer · answered Oct 30 '19 at 20:40

3

you can use this formula to color all na rows:

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

answered Oct 30 '19 at 20:40

player0

124,011
12
67
124

1

Thank you again @player0, once more you were able to help me thanks ! as long as they are highlighted I can just filter them by color and delete them. Thank you so much! – Killercamin Oct 30 '19 at 23:47
Hello, I ran the command and it has been highlighting the rows but it has only done 200 rows out of 7000. It has been already around 5 hours that I have left google sheets on. Is there any way to speed it up? – Killercamin Oct 31 '19 at 15:22
1

can you share a copy of your sheet? – player0 Oct 31 '19 at 15:46
1

can you try like this? https://docs.google.com/spreadsheets/d/1ud1QUq0fliq_I-SFV6cWvMp2ssKRMLbgEsxGS2yeOgo/copy – player0 Oct 31 '19 at 18:23
1

That's exactly what I was looking for, I don't know why my computer kept on taking forever to even highlight certain rows. You are a life saver. – Killercamin Oct 31 '19 at 19:15

score 2 · Answer 2 · answered Oct 30 '19 at 12:45

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

This is what rules I used

Here are another answers that may help you

Sorry for bad English.

score 1 · Answer 3 · answered Oct 30 '19 at 17:08

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

Removing a row containing a specific text in Google Sheets

3 Answers3