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.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!