How can I replace NaN with '#N/A' in a dataset and write it to Excel?

Hi~

I want to do some calculation and write the result to Excel. I want the null to be write as '#N/A' so that the manipulation will be easier in Excel. As an example, I write:

a=rand(3,2);
a(2)=nan;
A=dataset(a(:,1),a(:,2));
A.Properties.VarNames ={'a1','a2'};
export(A,'XLSFile','myA.xlsx')

but the null is a blank in Excel, I wonder how to replace it with '#N/A' . Any ideas?

Answer 1

[Mischa Kim]

Bond, you could use

 a      = rand(3,2);
 a(2,2) = NaN;
 b           = num2cell(a);
 b(isnan(a)) = {'#N/A'};
 bheader     = {'a1' 'a2'};
 filename = 'myA.xlsx';
 sheet    = 1;
 xlswrite(filename,bheader,sheet,'A1')
 xlswrite(filename,b,sheet,'A2')

Switching to a cell array allows for saving numeric and non-numeric values in the same object.

[Bond]

Thank you, Kim. Your answer is very helpful.