Text acts as zero

[Roger keyes]

I have been asked to tidy up a spreadsheet in Excel 2000. However some of the formula treat text as having a zero value. The formula are simple A1+A2 type i.e not in Sum() format. If I cut and paste these formula to a new spreadsheet excel acts as it should returning #value.

I can fix individual formula but there are many hundred and it would be useful if at least in the short term that the existing functionlity could be maintained. Any suggestions on why this works in the old spreadsheet and how to keep it in the new one would be greatly apreciated.

The spreadheet is very old and may have been created in excel 97 or even 95,

thanks

Roger

 

[ExcelPr0]

I need to udnerstand what you mean by this before posting a solution.
also, do you want the text as 0? (as mentioned in yr title?)

If I cut and paste these formula to a new spreadsheet excel acts as it should returning #value.

 

[Richard Schollar]

Hi Roger

Check out Tools>Options>Transition. If "Transitional FOrmula Evaluation" is checked, Excel will behave like this.

 

[Roger keyes]

Thanks the Tools - Options - transitions fixed it. Wish I'd asked yesterday instead of spending hours on it

 

[Makrini]

lol how many times have I had the same regret! hehehe