zero instead of #value! in cells operating text with text and text with numbers

Greetings, I need collaboration to unveil the formatting of a spreadsheet. A contractor sent a workbook which contains a worksheet with a formatting that yields zero values in all cells instead of #value! error when multiplying or otherwise operating with cells containing text with others also containing text. The same situation applies when the worksheet is requested to operate text and numbers. And those cells show blank on the screen but keep the formulas. No special formulas to hide #value! errors are being used.

When copying the mentioned worksheet to another empty worksheet in the same workbook, the #value! error appears.

Please find attached this example showing the situation.

Thanks for the assistance.

If you copy the worksheet and paste to a new worksheet the #VALUE errors appear, though you lose some of the formatting

On the TEST sheet, go to Office Button, select Excel Options, Selected Advanced and then scroll right to the bottom of the right pane. The Lotus Compatibility Settings is what allows you to "convert" text to 0's. If you uncheck the first one, the #VALUE errors will appear

Did you review my post?

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save