Log In
Register
Home
November 9, 2016
Offline
Hi everybody
I got a sheet with plenty of data. I'll like to delete cells containing only zero (0) values. Rather than deleting manually, is there a possibility of finding and and replacing all zeros with a dash, tick or leaving the cell blank
NB Don't delete the zeros in eg 10, 200, 50, etc but only cells whose value is a zero
Thank you
Meyanui
January 18, 2015
Offline
You could hide the "zeros" with cell formatting.
For example:
#,##0.00;-#,##0.00;;
or
#,##0;[Red]-#,##0;;
November 8, 2013
Offline
Hi Meyanui,
Another option is to go to Excel Options>Advanced, and clear the box that says: Show a zero in cells that have zero values.
If the solutions presented does not make you happy, here is another thing you can try.
Press the following key sequences:
1. Ctrl+h (open Find-Replace window)
2. Alt+t (open Options)
3. Alt+o (check the box for : Match entire cell content)
4. Alt+n (Find field, type here a zero, or whatever you want to replace)
5. Alt+e (Replace with field, leave empty or type the replacement value)
6. Alt+i (the keyboard equivalent of Find All button)
7. Ctrl+a (this will select all cells found by Find All action)
7. Alt+F4 (close the Find-Replace window.
At this moment, all the cells with a zero are selected. Press the Delete key to clear them, or, if you want to replace all with a specific value, just type the value and press Ctrl+Enter. All selected cells will be replaced with the value you just typed.
Note that you may skip some steps, Excel will keep in memory the choices you made for "Match entire cell content" for example, for the current excel session. If this checkbox is already checked, don't press Alt+O, otherwise it will clear that box.
Understanding how to use the Alt method is important here. Probably you know that the letter that needs to be typed to go to a specific field can be found in that field name: the letter is underlined. F i nd All button has the i underlined, so Alt+i will press the Find All button.
November 8, 2013
Offline
Frans Visser said
Only thing is you have to be careful about what you search for. In the case Meyanui brought in, he rightly said that the 10, 200, 50 and such should be untouched. But maybe with some clever thinking you can handle that as well?
That's why the search is performed with "Match Entire Cell content" option enabled, to avoid partial matches.
Cheers,
Catalin
1 Guest(s)
