CODEWITHSUNDEEP
google-sheetsspreadsheetfind-replace
curious
curious

Reputation: 23

Why does GSheet's "find" not find my blank cells even though ISBLANK() finds them?

I have the following problem: When I type "^\s*$" into GSheet's "find" it does not find my blank cells even though ISBLANK() finds them. I need to find and replace the blank cells with "NA". Help would be greatly appreciated!

This is an excerpt of my table: https://docs.google.com/spreadsheets/d/12EajCPW68UXc8kgeqfsEoxgTBuLbccdAnnj6tZOSntM/edit#gid=0

Upvotes: 1

Views: 2788

Answers (4)

Александр Ермолин
Александр Ермолин

Reputation: 1165

Although the documented "Find and Replace" feature should work with the ^s*$ regular expression on blank cells too, it does not indeed. Neither it works with \s* in conjunction with the "Match entire cell contents" flag.

I found a reasonable explanation here. The author suggests numbers formatting as "plain text" before "Find and Replace". Really it works and it is easy, because scripting is NOT required. So your steps can be as follows:

  • format numbers as "plain text" for the range in use
  • make replacements using the "canonical" regex ^\s*$ in "Find and Replace" dialog
  • format numbers back to "Auto" for the above range

Upvotes: 0

Kaness
Kaness

Reputation: 1

Answering this just in case anyone bumps into this issue.

What you need to do is

  1. While in the Find and Replace box, find the "Search using regular expressions" option. The "Match case" option will automatically be selected.

  2. In the Find box type in "^s*$" without the "" and you should be able to see those empty cells.

Upvotes: 0

Century Tuna
Century Tuna

Reputation: 1762

You can try this Apps Script code. This should get you started.

function replaceBlank() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = sheet.getRange(2,2, sheet.getLastRow(), 2);
  var rangeval = range.getValues();

  for (var i = 0; i < sheet.getLastRow()-1; i++) {
    Logger.log(rangeval[i]);
    if (rangeval[i].concat() == ",") {
    rangeval[i].splice(0,2,"NA","NA");
    sheet.getRange(i+2,2,1,2).setValues([rangeval[i]]);
    } else {
    }
  }
}

The way this code works is that it will iterate through columns B and C and once it detects that the current row is blank, it will set the value NA as defined on rangeval[i].splice(0,2,"NA","NA");

Screenshot: enter image description here

Upvotes: 0

player0
player0

Reputation: 1

try it like this:

=REGEXMATCH(C5, "^$")

^$

Upvotes: 0

Related Questions