Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.
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.
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.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
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.
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_
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.
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!
Had to select "more options" and setup some formatting rules on the Replace All Wizard.
Thanks for the answers guys!
Premium Content
You need an Expert Office subscription to comment.Start Free Trial