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.
LVL 2
axessJoshAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Professor JMicrosoft Excel ExpertCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
axessJoshAuthor Commented:
forgive my ignorance.

Where do I input that code?
Roy CoxGroup Finance ManagerCommented:
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.
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Read On!
Ess KayEntrapenuerCommented:
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_
axessJoshAuthor 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.
axessJoshAuthor Commented:
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 AnalystCommented:
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
axessJoshAuthor 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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.