How can I replace blank cells with zeros in Google Spreadsheet?

Question

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.

Answer 1

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

Answer 2

Use regular expression in Find and Replace:

^\s*$

Answer 3

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

Code V8

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

const zeroSheet = () => zero(sheet = SpreadsheetApp.getActiveSheet(), 'sheet')
const zeroDatarange = () => zero(range = SpreadsheetApp.getActiveSheet().getDataRange(), 'datarange')
const zeroSelection = () => zero(range = SpreadsheetApp.getActiveSheet().getActiveRange(), 'activerange')
const zeroManual = () =>  zero(spreadsheet = SpreadsheetApp.getActive(), 'manual')

const zero = (source, type) => {
  switch(type) {
    case 'sheet':
      range = source.getRange(1, 1, source.getMaxRows(), source.getMaxColumns())
      break
    case 'manual':
      range = source.getSheetByName('Sheetname').getRange(2, 4, 3, 6)    
      break
    case 'datarange':
    case 'activerange':
      range = source
      break
  }
  range.setValues(range.getValues().map(row => row.map(cell => !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:

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.
3. Reopen your document, you now have the new menu !

Answer 4

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

• Find: ^$
• Replace with: 0
• Search: Your range
• Select: Search using regular expressions

Answer 5

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

Answer 6

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.

Answer 7

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.

Answer 8

Use regular expression, which requires plain text formatting:

1. Select cells that include empty cells
2. Format > Number > Plain Text
3. Edit > Find and Replace
   1. Find: ^$
   2. Replace with: 0 (or whatever you want)
   3. Check "Search using regular expressions"
   4. Click "Replace all"