Remove N/A and 0s from Column

[Donal28]

Hi There

I have a data range B2:B129 and I would like to delete N/As and 0s from it just to show that these cells are blank

Any help on this would be very much appreciated

Regards
Donal

 

[Rick Rothstein]

I have a data range B2:B129 and I would like to delete N/As and 0s from it just to show that these cells are blank

Select the range B2:B129 and then press CTRL+H to bring up Excel's Replace dialog box. Type N/A into the "Find what" field, leave the "Replace with" field blank, click the "Options>>" button and make sure both checkboxes are not checked. Also make sure both text boxes at the top say "No Format Set" (if they say something else, click the downward pointing triangle on the button next to it and select the Clear option for that field). Then click the "Replace All" button. After that, replace the N/A in the "Find what" field with 0 and click the "Replace All" button again.

 

[Bingo969]

You can wrap your formula with the IFNA command with the modifier set to nothing. So for example something like CONCATENATE(B3,D3) would then become IFNA(CONCATENATE(B3,D3),""). That tells the formula to take anything that would result in NA and just leave it blank. IFNA is for NA and the last part with the 2 double quotes is what should be displayed. Leave it empty and it'll be blank.

You can also then go under Options > Advanced > scroll down to Display Options For This Worksheet and find the box for 'Show a zero in cells that have zero value' and make sure that's unchecked.

With both of those together any cell that should be NA or 0 will just be blank.

 

[Donal28]

Hi Rick

Thanks for the reply, that works perfectly much appreciated

Regards
Donal

 

[Donal28]

thanks for the reply Bingo969, that works too, much appreciated

Donal