how to replace 0 with blank column

**kuntalnr** · 04-15-2012, 01:36 PM

hi all
i have a column with numbers. may of them are zero. but for some reason i need the cells with zero as blank cells. can anyone help me with this?

**bebo021999** · 04-15-2012, 01:50 PM

Assumming working on A1:A10
=IF(A1:A10=0,"",A1:A10)
To use it in formula, must confirm with Ctrl-Shift-Enter after finishing the typing

**icestationzbra** · 04-15-2012, 02:05 PM

if you just want to hide the 0, you can do so using Conditional Formatting and use a colour of font for the text that melds into the cell's background. if the column of numbers is generated through some formulae, you can put conditions in the formulae to replace a '0' result with blank ("").

**Søren Larsen** · 04-15-2012, 02:12 PM

You could also use a custom format on your cells. To set up a custom number format, highlight the cells, then right click and choose "Format Cells". Click the "Number" tab. In the Category listbox, select "Custom". In the Type box, enter the following custom format:

0;0;

Then cells containing the value 0 will show as blank.

**Haseeb Avarakkan** · 04-15-2012, 05:33 PM

If you want to delete all zeros, use Find/Replace command.

Select the date, press CTRL+H to activate Find and Replace commnad

Find What: 0
leave Replace with empty, then under options select Match entire cell contents
Click Replace All