18

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.

2

8 Answers 8

13

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

1
  • 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. Dec 20, 2016 at 6:18
13

Use regular expression in Find and Replace:

^\s*$

Google spreadsheet replace blank with 0

2
  • 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. Apr 1, 2019 at 16:52
  • This was helpful thanks.
    – lathomas64
    Apr 17, 2019 at 17:38
8

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: 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
1
  • Worked like a charm. At least in the latest version you need to add it to your macros to execute it. Dec 13, 2018 at 20:45
3

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

  • Find: ^$
  • Replace with: 0
  • Search: Your range
  • Select: Search using regular expressions
1
  • 2
    -1. The question clearly states that this didn't work for the asker.
    – msh210
    Feb 6, 2019 at 8:41
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

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"

enter image description here

1
  • The only solution that worked for me!
    – Bach
    May 10 at 14:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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