How to remove only 0 (zero) values ​​from a column in excel 2010

Question

I want to remove values ​​from an integer column where the cell value is 0.

The resulting cells must be empty.

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 

Answer 1

I selected the columns that I want to remove 0, and then clicked DATA> FILTER. A filter icon appears in the column heading. I clicked on this icon and selected only 0 values ​​and clicked OK. Only 0 values ​​are selected. Finally, clear the contents or use the DELETE button. The problem is solved!

Answer 2

Press Control + H , then select Options and check Match entire cell contents and Match case . In the Find what field, enter a 0 and leave the Replace with field blank. Then replace everything. This will remove all zeros that are autonomous.

Answer 3

The easiest way for me was to create an extra column with an if statement, which would essentially be used as a copy from the clipboard.

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

This should return the adjusted column, which can then be copied and pasted back to the original AS TEXT column - if you just copy the insert, a circular reference will be executed and all data will be erased from both columns. Do not worry if you have not read this carefully. Ctrl + Z is your best friend ...

Answer 4

This is not a great answer, but it should lead you to the right one. I did not write VBA for a solid minute, so I can’t remember the exact syntax, but there is some kind of "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, VBA goes through each line. If the cell in this row is an integer and equal to zero, just replace it with "". It will not be empty, but it seems to be. I think there is also a cell.value is empty property that can clear the contents of a cell. Use the library in VBA, I'm sure there is something there.

Alternatively, if this is a one-time task, you can use a special filter. Just select the filter from the tape and replace all 0 s in a line with a space.

Hope this helps you get started.

Answer 5

You should not use the space "instead of" 0 "because excel treats the space as a value.

So, the answer with the option (Ctrl + F). Then click the options and put in Find with "0". Then click (Match the entire contents of the cell. Finally, replace or replace everything before you.

This decision can give more. You can use (*) before or after the values ​​to remove any parts you want.

Thanks.

Answer 6

The solution (Ctrl + F) is really close - only the last step in this process has not been formulated. Although the author is right in space = "0", this will have nothing to do with this method. The data you are looking for (everything you want to delete) can be anything.

Find the data you want to delete (in the "Find" field). In the Replace box, leave the box blank. Then replace or replace everything. Cells with this specific data will be empty.

Thanks for meeting me on the right track.

Answer 7

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

Answer 8

I selected the columns that I want to remove 0, and then clicked DATA> FILTER. A filter icon appears in the column heading. I clicked on this icon and selected only 0 values ​​and clicked OK. Only 0 values ​​are selected. Finally, clear the contents or use the DELETE button.

Then remove blank lines from deleted 0 values. I click DATA> FILTER I clicked on this filter icon, and unselected spaces copy and paste the remaining data into a new sheet.

Answer 9

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

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

In the "Format" section, change the color of the text to white or any other background, and all cells that contain exactly zero will disappear.

Obviously, this also works with any other value that you want to delete.

Answer 10

Consider your data in column A and now write the encoding

 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 

Answer 11

(Ctrl + H) → Find and replace the window opens → Find what is “0”, replace with “(leave the field empty) → click options correspond to all the contents of the cell → click the“ Replace All ”button.

Answer 12

The easiest way is this: Click the "Office" button (top left) Click "Excel Options" Click "Advanced" Scroll down to "Show options for this worksheet" Unlock the box "Show zero in cells with a zero value" Click " okay "

That is all that is needed.

:)