Remove 0.00 data from Excel spreadsheet

axessJosh
axessJosh used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Microsoft Excel Expert
Top Expert 2014
Commented:
try this

Sub Macro1()
For Each c In ActiveSheet.UsedRange
    If Len(c.Value) = 1 And c.Value = 0 Then
        c.Value = ""
    End If
Next c
End Sub

Open in new window

Author

Commented:
forgive my ignorance.

Where do I input that code?
Roy CoxGroup Finance Manager

Commented:
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.
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

Commented:
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_

Author

Commented:
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.
I was able to figure it out.

Had to select "more options" and setup some formatting rules on the Replace All Wizard.

Thanks for the answers guys!
Rob HensonFinance Analyst

Commented:
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

Author

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial