Unrecognized token 21 in formula

Topics: User Forum
Aug 17, 2010 at 3:53 PM

Interesting one I'm trying to troubleshoot.

The document in question can be found at

When PHPExcel_IOFactory::load() is executed on the above file, I get an exception with "Unrecognized token 21 in formula". (full error message posted below)

I tried digging into this and researched the Microsoft Excel File Format document listed in another thread and all I get is 0x21 is a "function with fixed number of arguments".

The script appears to break at H22 which is weird because the exact same formula (with different cell references) appears in H16 on down. I tried whacking H22, and the error then occurs with H23.



Full Error Message

Fatal error: Uncaught exception 'Exception' with message 'Unrecognized token 21 in formula' in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\Excel\PHPExcel\Reader\Excel5.php:4421 Stack trace: #0 C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\Excel\PHPExcel\Reader\Excel5.php(3744): PHPExcel_Reader_Excel5->_getNextToken('!??L????A????? ...', 'H22') #1 C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\Excel\PHPExcel\Reader\Excel5.php(3728): PHPExcel_Reader_Excel5->_getFormulaFromData('L????A??L????L?...', '', 'H22') #2 C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\Excel\PHPExcel\Reader\Excel5.php(830): PHPExcel_Reader_Excel5->_getFormulaFromStructure('L?L????A??L????...', 'H22') #3 C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\Excel\PHPExcel\IOFactory.php(191): PHPExcel_Reader_Excel5->load('C:/Program File...') #4 C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\Portal\functions\upload_bom.php(40): PHPExcel_IOFactory::load('C:/Program File...') in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\Excel\PHPExcel\Reader\Excel5.php on line 4421

Aug 20, 2010 at 12:12 PM

Simple fix:

On or about line 4724 in classes/PHPExcel/Reader/Excel5.php, which should read:

  case 0x41: // function with fixed number of arguments


  case 0x21: // function with fixed number of arguments

I'll need to look more closely to see if there are any adverse effects, but this does then read your workbook successfully.

Aug 20, 2010 at 12:37 PM

That WAS a simple fix! Good job MarkBaker!