Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I received an e-mail with an Excel file formatted in Numbers, but where there should be 0s, there are dashes "-".

Strange thing is that the person who sent it to me sees 0s, and I also need it that way because I need to compute means (I need numbers only, not dashes).

How can I transform my "-" into 0, and why do I have this problem?

share|improve this question
1  
The easiest answer, assuming you do not have any "-" anywhere else in the document, would be to select everything and then press "crtl+F", click on the replace tab and replace "-" with "0". However if your friend is seeing 0's and not -'s then there may be other problems. Can you post a screen shot of the formula bar when you have a cell with this problem selected? –  Kevin Anthony Oppegaard Rose Feb 7 '13 at 15:11
1  
It could be a custom Format. Have you tried selecting the cell and changing the format to 'Number' (you can select this in the Home ribbon bar). –  Brad Patton Feb 7 '13 at 15:55

1 Answer 1

It seems likely that @Brad is correct (so Kevin’s suggestion may not work). That the contents of these cells are zero – it is just that your set up has been instructed to display (format) 0 as - (for example enter 0 somewhere then select that cell and click Home/Number/). However, since actually zero, the representation should make no difference to any calculations.

Custom formats are saved with the workbook, so this could be why you are not seeing the format you are used to (ie the format was chosen by the person who sent you the file).

Custom number formats allow independent control of the display of positive, negative, zero and text values (in that order) with use of the semi-colon separator. Hence:

[Blue]#,##0.00_);[Red](#,##0.00);-;"Annual "@

  • Positives: in blue, comma separator for thousands, rounded to two decimal places (with period) and trailing (for alignment with negatives) space.
  • Negatives: in red, in parentheses, comma separator for thousands, period for decimal, rounded to two decimal places.
  • Zero: -
  • Text: Preceded by ‘Annual ’.

Not all four sections need be used, for example [Blue]#,##0.00_);;-;"Annual "@ would not display negatives, though these would still be available for calculation (which could be very confusing). Similar but probably more useful would be [Blue]#,##0.00_);[Red](#,##0.00);; which suppresses display of zeros values in any fashion (and also text).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.