MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How to change all zeros in a spreadsheet to a hyphen " - "

Posted by Neil Reekie on January 11, 2002 6:45 AM

I have a speadshhet and I would like to be able to convert all the zero values to a dash/hyphen or whatever it's called ( " - " ). It can be done in lotus 1-2-3 and we're in the process of converting to excel. Any help would be appreciated.



Posted by Andy gee on January 11, 2002 6:47 AM

Just use find and replace.

Press Control+F
Click Replace
Enter 0 in the top box
Enter - in the bottom
Click replace all

Remember to back up first as it may not be undoable!

Posted by Mark W. on January 11, 2002 7:00 AM

If you follow Andy's advice below... sure and check the "Find entire cells only"
check box!

Posted by Mark W. on January 11, 2002 7:02 AM

In the event that you'd just like to format zeroes...

...use one of the built in Accounting formats or
customize your own. For example, 0;-0;"-" will