Link to home
Start Free TrialLog in
Avatar of axessJosh
axessJosh

asked on

Remove 0.00 data from Excel spreadsheet

I have a spreadsheet that I need to do a mail merge from.  Unfortunately, the person that input the information left 0.00 if there was no value.

My mail merge template is setup to pull only if data is not blank.  Well, with 0.00, that cell is not blank.  It will be too complicated to redo my merge template, is there a way to remove only the 0.00 from my spreadsheet?

I tried Replace All, but unfortunately, that replaced zeros in other values and messed some things up.

I also noticed that on the Find Replace, 0.00 is not recognized because it's set from the Currency Format change.

I really just need a way to delete data from all cells if it equals 0 or 0.00 only.
ASKER CERTIFIED SOLUTION
Avatar of Professor J
Professor J

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of axessJosh
axessJosh

ASKER

forgive my ignorance.

Where do I input that code?
Can you try hiding zeros:-

Click File > Options > Advanced.

Under Display options for this worksheet, select a worksheet, and then do one of the following:

To display zero (0) values in cells, check the Show a zero in cells that have zero value check box.

To display zero (0) values as blank cells, uncheck the Show a zero in cells that have zero value check box.
Display or hide all zero values on a worksheet

Click the Microsoft Office Button Office button image, click Excel Options, and then click the Advanced category.

Under Display options for this worksheet, select a worksheet, and then do one of the following:

To display zero (0) values in cells, select the Show a zero in cells that have zero value check box.

To display zero values as blank cells, clear the Show a zero in cells that have zero value check box.

there are other options,  visit this office support page to get all of the different methods


https://support.office.com/en-us/article/Display-or-hide-zero-values-3ec7a433-46b8-4516-8085-a00e9e476b03#bmdisplay_or_hide_all_zero_values_on_a_
Both options only remove them from displaying in Excel. When merged to Word through Mail Merge, the values still show.  I need to actually remove the "0" values from the spreadsheet.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Find and Replace will do it without formatting.

Find:  0
Replace:  Leave blank

Click Options button and select the "Match Entire Cell contents".

Thanks
Rob
I was able to fix this using the native Excel tools without having to use the Macro solution.  While I'm sure that answer was another correct options, I was unable to use it since I don't know how to use VBA script in Excel.