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.
Google Sheets – How to Replace Blank Cells with Zeros
google sheets
Related Solutions
Whether or not this is practical may depend on the formulae you are using (which you don’t show) but for something very simple like =Sum(A1:B2) you might switch to View, All formulae and replace ( with ($ and : with :$. Then transpose, repeat the process and transpose back. Otherwise “Search using regular expressions” may help.
Use the following formula to accomplish that.
Formula
=QUERY(
'GR Live Campaign Tracker'!J:L, // data range
"SELECT J, COUNT(L) WHERE L IS NOT NULL GROUP BY J", // select
0 // headers
)
Screenshot
Example
Added the formula into your example file.
Related Topic
- Google Sheets – How to Categorize Data Using Regular Expressions
- Google-sheets – How to get the number of cells used in a Google Sheets spreadsheet
- Google-sheets – Trailing zeros in Google Spreadsheet automatically removed from form submission
- Google-sheets – Sliding window average of last N non-empty non-blank cells in Google Sheets
- Google Sheets – Combining IMPORTRANGE, SUMIFS, QUERY with Mixed Content
- Google-sheets – How to include blank cells when using an Array Formula
- Google-sheets – How to show blank cells instead of 0s in Google Sheets
Best Answer
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