Calculate Formulas giving unexpected result...

Topics: Developer Forum, User Forum
Sep 1, 2014 at 12:53 AM
Been trying to get the same results in PHPExcel as calculated by Excel. Currently working with a spreadsheet that is designed by another and it is prefered not to change the current formulas from what they are...

The Forumla that seems to be a problem is as follows:
=len(I9)-len(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(I9,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,""))
Input on 'I9' will be a twelve digit numnber that has 2 leading zeros such as:
001234567891
Now this will return a result of 10 based on the formula while:
001234567890
gives a result of 9
Is this a PHPExcel issue or a problem with the actual forumla??

Can I get '001234567890' to return 10 using PHPExcel without changing the forumla?

Any help is appreciated.
Sep 1, 2014 at 5:06 AM
It seems messing with the formula is showing what may be a potential bug...

Changing the formula to wrap the Zero in quotes gives expected results
=len(I9)-len(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(I9,"0",""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,""))
Additionally order does not matter it is the 0=ZERO that matters and causes the problem.