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

9 views (last 30 days)
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?

Accepted Answer

Mischa Kim
Mischa Kim on 15 Mar 2014
Edited: Mischa Kim on 16 Mar 2014
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.

More Answers (0)

Categories

Find more on Data Import from MATLAB in Help Center and File Exchange

Tags

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!