How can I replace NaN with '#N/A' in a dataset and write it to Excel?
9 views (last 30 days)
Show older comments
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?
0 Comments
Accepted Answer
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)
See Also
Categories
Find more on Data Import from MATLAB in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!