Replacing NULL in a column with zero
I need a formula(s) to use that can replace "NULLS" in a worksheet with zero or replace nulls with "Blank". One column contains text values and the other column number values
- A formula to replace "NULLS" with zero in a number value column
- Another formula to replace "NULLS" in a text value column with "Blank"
Best Answer
-
Hi! Apologies if this is a dumb clarification to ask, but are you referring to an error message you're receiving that you'd like to replace with "0" or "blank," OR do your cells have the text, "null"?
If the issue is a formula generating an error message, you can use the IFERROR function -
IFERROR
(your formula….), "
0"
to enter 0 orIFERROR
(your formula….), ""
to leave the cell blank.If your cells actually have the text, "null," you could highlight the column and use ctrl+F to replace the text with a 0 or blank.
Or, you could use the formula,
=IF([column name]@row="NULL", "0", [column name]@row
that would enter "0" if the cell has "NULL" and copy the cell contents if it's something else/a value you want to keep.
Answers
-
Hi! Apologies if this is a dumb clarification to ask, but are you referring to an error message you're receiving that you'd like to replace with "0" or "blank," OR do your cells have the text, "null"?
If the issue is a formula generating an error message, you can use the IFERROR function -
IFERROR
(your formula….), "
0"
to enter 0 orIFERROR
(your formula….), ""
to leave the cell blank.If your cells actually have the text, "null," you could highlight the column and use ctrl+F to replace the text with a 0 or blank.
Or, you could use the formula,
=IF([column name]@row="NULL", "0", [column name]@row
that would enter "0" if the cell has "NULL" and copy the cell contents if it's something else/a value you want to keep.
-
Hello Jen,
The the column actually had the text "NULL" so each of the answers worked perfectly. Thank you so much.
-
Great - glad that worked for you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!