Register
Log In
Unanswered
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.
Dashboards
Power BI
Power Query
April 21, 2015
Offline
I think this solution of Catalin is great and after some trial and error you can use it.
I remember that I somewhere saw also an instruction how to do this, with a video as well. But couldn't find it at once.
What I found however is also a nice (written) instruction that might be handy in this cases and has a different approach.
You can see it on the website of the friends of Exceljet: https://exceljet.net/lessons/how-to-find-all-matches-at-once-in-excel
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?
Dashboards
June 25, 2016
Offline
This method as suggested by Catalin (and ExcelJet) is very useful as it allows all kinds of search/select/replace without using a macro. It can also search for formats as well.
I always use this method to locate and highlight unprotected cells in my worksheet.
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
Most Users Ever Online: 57
Currently Online: Mynda Treacy
13 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
Md. Rahman
Lilian Vo
Jane Kumwenda
David Miller
Efe Onwochei
LynAnn Williams
Brandy Kriger
purna chandar
E K
velissa barr
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas
