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.
Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.
Sign up to join this communityI 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.
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
The following script will add a 0
to every empty cell in the range.
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)))
}
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:
I've created an example file for you: Replace blank cell with zeros.
You can use "Find and Replace" (Ctrl+F → More options).
^$
For a limited number of columns you might filter to select Blanks, then insert and copy down 0
.
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 1
s instead of 0
s with PRODUCT()
, so I did:
=PRODUCT(SUM(A,0), multiplier)
which gave me expected results for empty or non-number cells in A
.
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.
Use regular expression, which requires plain text formatting:
^$
0
(or whatever you want)