xlsread in Matlab returns NaN even there is no data in Excel

Question

I use xlsread to read a xlsx file. I expect the rawdata will return a 34x3 cell array.

[raw_num raw_txt rawdata]=xlsread('file.xlsx');

The raw_num and raw_txt return correct value, 34x1 cell array and 34x2 cell array.

However, when I check rawdata, it returns 139x6 cell array. It contains not only data in excel file but also NaN element. For the NaN element, there is no data in excel file. I can only assume that I might type something before and then I delete it. But why matlab read it?

The rawdata looks like this:

'a' 'b' 'c' NaN NaN

'd' 'e' 'f' NaN NaN

NaN NaN NaN NaN NaN

How can I avoid this when I use xlsread?

Or how can I remove NaN after I got this matrix?

Thanks for help

Answer 1

If NaN elements are only at the edges of the matrix like shown in your example, you could first remove rows with all NaN, then remove the columns with all NaN.

% test data
A = {NaN, NaN, NaN, NaN; NaN, NaN, NaN, NaN; NaN, NaN, NaN, NaN};
A{1, 1} = 'a';
A{1, 2} = 'b';
A{2, 1} = 'c';
A{2, 2} = 'd';

fh = @(x) all(isnan(x));
% remove rows with all NaN
A(all(cellfun(fh, A),2),:) = [];
% remove columns with all NaN
A(:,all(cellfun(fh, A),1)) = [];

The reason you have to do it this way is because cellfun does not preserve dimensions, so you have to run it on each dimension individually. Alternatively, you could write a for loop.