xlsread returns NaN for cell references to string

I'm reading an excel document with xlsread with references to other cells that be either text or numeric values. The problem is that xlsread seems to interpret cells starting with an "=" as numeric even if is a reference to a cell with text. Thus the result of a reference to text cell returns NaN, even in the raw format. Is there any way to work around this? I've tried some tricks (see below) that forces Excel to treat the cell as text but it doesn't work with xlsread. The (very) simple example below illustrates the issue. Example:

      A          B         C             D
  1   My         age       is           10
  2   =A1        =B1&""    =TEXT(C1;0)  =D1

> [num text raw] = xlsread('book1.xls')
num =
   NaN   NaN   NaN    10
   NaN   NaN   NaN    10
text =
  1×3 cell array
    'My'    'age'    'is'
raw =
  2×4 cell array
    'My'     'age'    'is'     [10]
    [NaN]    [NaN]    [NaN]    [10]

Matlab 2016b, Excel 2010 (xls format), Linux.

[jean claude]

try to remove all = signs by find/replace function in excel, replace = by spacebar

[Anders Bergåker]

I don't quite follow but perhaps my question was unclear. Line 2 in my example shows the formulas I've put in Excel, not the result. In Excel Line 1 and Line 2 looks identical.

[Fangjun Jiang]

For your information, I tried this: 
MATLAB R2016b
Windows 7
Excel 2010
Excel file saved as .xls (Excel 97-2003 Workbook)

The cell is read as its value, not NaN. For example, my cell C1 is "=SUM(A1:B1)" but it was read as its numeric value.

[Anders Bergåker]

Thank you but the problem arises when the referenced cell contains an test/string. I.e. A1: "Hello", C1: "=A1"

[Image Analyst]

If it's a string (enclosed in double quotes), it's a string, and will/should return a string. If you get rid of those double quotes, it will treat it as a formula and should return the numbers, like Fangjun got.

[Anders Bergåker]

Thanks for you suggestions, but the problem is that when a function returns a string xlsread still treats it as a number. I'll try to provide a more direct example:

 Excel input:
     A      B
  1  Hello  =A1

 Excel shows:     
     A       B
 1  Hello   Hello

Matlab returns:
>> [num, text, raw] = xlsread('doc/book1.xls')
raw = 
    'Hello'    [NaN]

My problem is that cell B1 is treated a a numerical value despite the fact that it references a string. I want xlsread to return 'Hello' for cell B1 as well. I was hoping there would be a workaround for that somehow.

Note that xlsread uses the basic mode on Linux.

[Fangjun Jiang]

FYI, with your example file, xlsread() returns value, not NaN or formula. Maybe you should report this to the Mathworks tech support.

>> [num, text, raw] = xlsread('test.xls')
  num =
       []
  text =
    1×2 cell array
      'Hello'    'Hello'
    raw =
    1×2 cell array
      'Hello'    'Hello'

[Walter Roberson]

The behavior is different in basic mode, which is for any system that is not windows with excel installed. The poster mentioned basic mode and Linux.

[Anders Bergåker]

Ty Jiang, this flaw only seems to exist in the basic implementation of xlsread. The PC version (i.e. non basic) returns the same values as Excel which is the behavior I was looking for.

Answer 1

[Walter Roberson]

On MS Windows systems with Excel installed, it is possible to create an activexserver object to talk to excel in order to extract macro text or execute macros.

On non-Windows systems or MS Windows systems without Excel, or when 'basic' mode is specifically asked for, in order to read .xlsx files, a MATLAB routine xlsreadXLSX is called. The routine calls upon some java routines to extract portions of the .xlsx (because .xlsx are directories of .xml files that have been ZIP'd together) into text form. xml text form in hand, the MATLAB routine calls upon regexp() to parse the xml. Worst case, that code could be copied and hacked to know about formulae.

On non-Windows systems or MS Windows systems without Excel, or when 'basic' mode is specifically asked for, in order to read .xls files a MATLAB routine xlsreadBasic is called. It invokes the internal MATLAB routine biffread to do the binary read and discover the headers, but then it calls upon the built-in routine biffparse to turn the bytes into data. There is no configurability there. Handling macros would require writing or finding an xls parser. I seem to recall having encountered one in Java.

[Anders Bergåker]

Thanks you. I did look into the files but bifparse is a mex-file so there is no way to investigate further.

I found a similar function readtable(), that seems return data in a correct format. I'll attempt to rewrite my function using tableread instead.