nan values from matlab to excel

8 views (last 30 days)
Carolina
Carolina on 24 Apr 2014
Commented: dpb on 25 Apr 2014
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!
  5 Comments
Carolina
Carolina on 25 Apr 2014
Thank you dpb! Your suggestion worked better than I had imagined when I first asked the question. Thanks! ;)
dpb
dpb on 25 Apr 2014
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.

Sign in to comment.

Answers (1)

Justin
Justin on 24 Apr 2014
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.

Tags

Community Treasure Hunt

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

Start Hunting!