11

I have a spreadsheet where someone else entered the data. They left blank cells instead of recording 0 values. How can I replace the blank cells with 0s?

I tried find and replace with regular expressions using ^$, but it did not work.

12

I assume you only want to do this in a certain part of the spreadsheet, and that it's a one-off problem which you need to fix, not something you need to repeat.

For this, the simplest approach is to find another area the same shape and size (perhaps in another worksheet), and not currently used.

In each cell in that area, set the forumula to something like this (depending on the cells you use):

=if(isblank(A3),"0",A3)

The select the data from the 2nd area, copy it, and paste it back to the original using Edit > Paste Special > Paste values only

You may need to re-apply some of the formatting (eg date formats), but the values will be there.

fyi, I have put a worked example of this approach here: https://docs.google.com/spreadsheets/d/1T6owfGtpKt3NLl5hq1AZpeWA1ILWUwBQ44bdlAmekF0/edit?usp=sharing

  • 2
    This works wonderfully. I did have a small issue with using the SMALL command on the result. I was able to fix it by using =if(isblank(A3),0,A3) Instead. – Joe Manlove Dec 20 '16 at 6:18
7

The following script will add a 0 to every empty cell in the range.

Code

function onOpen() {
  SpreadsheetApp.getUi().createMenu("Zeros")
    .addItem("Sheet", "zeroSheet")
    .addItem("Data range", "zeroDatarange")
    .addItem("Selection", "zeroSelection")
    .addItem("Manual Override", "zeroManual")
  .addToUi();      
}   

function zeroSheet() {
  var sheet = SpreadsheetApp.getActiveSheet();
  zero(sheet, 'sheet');
}

function zeroDatarange() {
  var range = SpreadsheetApp.getActiveSheet().getDataRange();
  zero(range, 'datarange');
}

function zeroSelection() {
  var range = SpreadsheetApp.getActiveSheet().getActiveRange();
  zero(range, 'activerange');
}

function zeroManual() {
  var spreadsheet = SpreadsheetApp.getActive();
  zero(spreadsheet, 'manual');
}

function zero(source, type) {
  var data, range;
  switch(type) {
    case 'sheet':
      var lastRow = source.getMaxRows();
      var lastColumn = source.getMaxColumns();    
      range = source.getRange(1, 1, lastRow, lastColumn);
      break;
    case 'manual':
      range = source.getSheetByName('Sheetname').getRange(2, 4, 3, 6);    
      break;
    case 'datarange':
    case 'activerange':
      range = source;
      break;
  }
  data = range.getValues();
  range.setValues( data.map( function(row) {
    return row.map( function(cell) {
      return !cell ? 0 : cell;
    });
  }));
}

Note

When the file is opened, a menu item called Zeros is created, having four sub items. If you want to run the manual function upon opening as well, then simply add it like so: enter image description here

Example

I've created an example file for you: Replace blank cell with zeros.
1. Create a copy of the file or paste the code under Tools>Script editor. 2. Press the clock bug button to authenticate the script. enter image description here 3. Reopen your document, you now have the new menu ! enter image description here

  • Worked like a charm. At least in the latest version you need to add it to your macros to execute it. – Nic Szerman Dec 13 '18 at 20:45
6

Use regular expression in Find and Replace:

^\s*$

Google spreadsheet replace blank with 0

  • For my situation, this was the best answer. I had two ranges of cells that needed to have values, but were empty. I filled in some cells with values, if the data was available for them. Then I wanted all the other empty cells to be filled with zero to indicate I'd already considered those cells. – L S Apr 1 at 16:52
  • This was helpful thanks. – lathomas64 Apr 17 at 17:38
2

For a limited number of columns you might filter to select Blanks, then insert and copy down 0.

2

One way that has worked for me in similar situations on Google spreadsheets was to SUM the empty column with 0 and store the result in a new column.

If column A has the empty cells you want to be replaced and, say, you have data in B, then use the next empty column C to store the updated values like so:

C1=SUM(A1, 0) and so on for the entire column C.

In my case, I needed to multiply values and empty cells worked like 1s instead of 0s with PRODUCT(), so I did:

=PRODUCT(SUM(A,0), multiplier)

which gave me expected results for empty or non-number cells in A.

1

I had a huge data with blanks randomly set inside it, so using if(isblank()) was a bad idea for me as it would have been super labor intensive.

Instead, I downloaded my sheet as a csv, and opened it in TextWrangler (any text editor with find and replace will work). Then I found ',,' and replaced with ',0,'. I had to do that twice. Then when I reimported, I used if(isblank()) only on the first and last columns of my data set. Everything else was zeroed out. Only took about 5-10 mins.

1

You can use "Find and Replace" (Ctrl+F → More options).

  • Find: ^$
  • Replace with: 0
  • Search: Your range
  • Select: Search using regular expressions
  • 1
    -1. The question clearly states that this didn't work for the asker. – msh210 Feb 6 at 8:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.