Replacing NULL in a column with zero

daoppong

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

1. A formula to replace "NULLS" with zero in a number value column
2. Another formula to replace "NULLS" in a text value column with "Blank"

Jennifer Kurtz

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 or

IFERROR(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.

Jennifer Kurtz

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 or

IFERROR(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.

daoppong

Hello Jen,

The the column actually had the text "NULL" so each of the answers worked perfectly. Thank you so much.

Jennifer Kurtz

Great - glad that worked for you!