[excel-formula] How to remove only 0 (Zero) values from column in excel 2010

I want to remove the values from entire column where cells value is 0.

The resultant Cells should be blank.

How can I write a formula for this? Any suggestions?

TELEPHONE NUMBERS
----------
49 5235102027   <-- Cell has 0 value but shouldn't removed!
35 2578945655
0               <-- Remove this   
90 5322720638
48 5396329155

Easiest way for me was to create an extra column with an if statement to essentially use as a copy past clipboard.

=IF(desired cell = 0, "", desired cell)

That should return a corrected column which then you can copy and paste back over the original column AS TEXT--if you just copy paste a circular reference occurs and all the data is "erased" from both columns. Don't fret if you didn't read this carefully Ctrl+Z is your best friend...

(Ctrl+H) -> Find and Replace window opens -> Find what "0" ,Replace with " "(leave it blank )-> click options tick match entire cell contents -> click "Replace All" button .

The (Ctrl+F) solution is really close - just the final step in the process was not articulated. While the author is correct about a space = "0", it won't make any difference with this method. The data you search for (whatever you want to delete) can be anything.

Search for the data you want to delete (in the "Find" field). In the "Replace" field, leave it blank. Then Replace or Replace All. The cells with those specific data will be emptied.

Thanks for getting me on the right track.

When I have used replace all 0 and match case with blank in Excel 2010 I find it paints the cell blank but the data is just whitewashed. So you use counta and the cell is still counted as with something to count. Never use that method in 2010 unless it is for display purposes only.

The easiest way of all is as follows: Click the office button (top left) Click "Excel Options" Click "Advanced" Scroll down to "Display options for this worksheet" Untick the box "Show a zero in cells that have zero value" Click "okay"

That's all there is to it.

:)

There is an issue with the Command + F solution. It will replace all 0's if you click replace all. This means if you do not review every zero, zero's contained in important cells will also be removed. For example, if you have phone numbers that have (420) area codes they will all be changed to (40).

Some of the other answers are great for removing zeros from existing data, but if you have a working sheet that is constantly changed and want to prevent zeros from ever appearing, I find it's easiest to use conditional formatting to make them invisible. Just select the range of cells you want to apply it to > conditional formatting > new rule.

Change the rule type to "format only cells that contain" Cell value > equal to > 0.

Under "Format" change the text colour to white or whatever your background happens to be, and all cells which contain exactly zero will disappear.

Obviously this also works with any other value you want to make disappear.

Press Control + H, then select Options and check Match entire cell contents and Match case. In the Find what field type a 0, and leave the Replace with field blank. Then Replace All. This will remove all of the zeros that are stand alone.

You shouldn't use a space " " instead of "0" because the excel deal with the space as a value.

So, the answer is with the option by (Ctrl + F). Then, click the options and put in the Find with "0". Next, click (Match entire cell contents. Finally, replaced or replaced all up to you.

This solution can give more also. You can use (*) before or after the values to delete any parts you want.

Thanks.

This is not a great answer, but it should lead you to the proper one. I haven't written VBA in a solid minute so I can't recall the exact syntax, but here's some 'psudeo code' for you -- I know you can easily implement this in a VBA macro

for all worksheet.rows
    if cell.value == 0
        then cell.value = " "
    endif
endfor

Basically, have VBA run through each row. If a cell in that row is an integer and equal to zero, simply replace it with a " ". It won't be blank but it'll seem to be. I think there's also a property called cell.value is empty that might clear cell contents. Use the library in VBA, I'm sure there's something in there you can use.

Alternatively, if this is a one time job, you can use a special filter. Just select filter from the ribbon and replace all 0 s by row with a space.

I hope that helps to get you started.

Consider your data is into column A and will write coding now

Sub deletezeros()
    Dim c As Range
    Dim searchrange As Range
    Dim i As Long

    Set searchrange = ActiveSheet.Range("A1", ActiveSheet.Range("A65536").End(xlUp))

    For i = searchrange.Cells.Count To 1 Step -1
        Set c = searchrange.Cells(i)
        If c.Value = "0" Then c.EntireRow.delete
    Next i
End Sub

This is the correct answer to auto hide of zero value and cell shows blank for zero value only follow:

1. Click the office button (top left)
2. Click "Excel Options"
3. Click "Advanced"
4. Scroll down to "Display options for this worksheet"
5. Untick the box "Show a zero in cells that have zero value"
6. Click "okay"

I selected columns that I want to delete 0 values then clicked DATA > FILTER. In column's header there is a filter icon appears. I clicked on that icon and selected only 0 values and clicked OK. Only 0 values becomes selected. Finally clear content OR use DELETE button.

Then to remove the blank rows from the deleted 0 values removed. I click DATA > FILTER I clicked on that filter icon and unselected blanks copy and paste the remaining data into a new sheet.