nan values from matlab to excel

Hi everyone! I am trying to export some data from MatLab to Excel that contains some cells that are "nan's". This is part of the data and needs to stay. Excel doesn't seem to recognise MatLab's nan's and I found a reply to someone else suggesting using this code:

 a      = rand(3,2);
 a(2,2) = NaN;
 b           = num2cell(a);
 b(isnan(a)) = {'#N/A'};

The problem with it is that it shows up as: '#N/A' instead of just #N/A, which defeats the purpose... Any ideas?

Thank you!

[dpb]

Excel doesn't have a #NaN builtin, at least thru the release I have (just tested it).

The #N/A is as close as you can get.

xlswrite('test,xls',{'#n/a'})

worked here ok. Which is what the suggestion is; not the string #NaN which will be interpreted by Excel as a just the string, not a special value.

[Carolina]

Hi dpb

My mistake, i meant #N/A. Excel doesn't show the nan's in the graphs. However, with the code I had it just sees it as a string (because of the quotation marks) and plots it as zero.

So, my problem specifically is the quotations marks.

In terms of reading it on MatLab, i am saving a different file for that purpose.

Thank you

[dpb]

I just tested precisely the above code here -- works as expected including drawing a graph in which it skipped the location w/ the #N/A value and entering a formula referencing that cell that propagated the missing value.

Are you sure your write is to the sheet you think it is and you're opening the correct one and not some previously saved version or somesuch? It surely looks to be fine here -- there are no quotes in the Excel cells on the above export; it recognized the missing value identifier correctly.

[Carolina]

Thank you dpb! Your suggestion worked better than I had imagined when I first asked the question. Thanks! ;)

[dpb]

So IOW you hadn't actually tried the original suggestion but inferred from the form of output from the workplace that the quotes in the display of b at the command line were going to be a problem? They're not real; they're a fignewton of the display of a cell string just as the [] surrounding the numeric values aren't real but indicative of the content of a cell array rather than a native double.

Answer 1

[Justin]

To expand on dpb's comment there are a few possible options and it depends on what you want to do and if you want to be able to read the data back in. It seems like dpb's option would be the best because excel would recognize that as a unusable value but Matlab would read it back in differently.

   xlswrite('test.xls',{'1'; '#n/a'; 'NaN'})

   [a, b, c] = xlsread('test.xls')
a =
     1
b = 
    '#N/A'
    'NaN'
c = 
    [1]
    'ActiveX VT_ERROR: '
    'NaN'

If you need to read it back in you could convert any 'ActiveX VT_ERROR: ' to a NaN of course and that would work also.