I'm reading an Excel file via xlsread and creating a cell array of the raw values: [~, ~, raw_values] = xlsread(filename,'sheet1');

Some cells in the sheet previously contained values, which have since been deleted. However, the dimensions of raw_values still cover the range where these values used to be. Thus, raw_values contains a lot of trailing columns which contain nothing but NaNs.

I can't see a difference in Excel between the cells which previously contained values and those that didn't. The formatting of both is 'Standard'. However, if I use the Format Painter to copy the formatting from untainted cells to the affected ones, the column no longer get's imported by xlsread.

How can I prevent these trailing NaN columns (and rows) from being created by xlsread or delete them in an elegant way?

CodePudding user response：

First off: xlsread is not recommended starting in R2019a (see doc). Hence if you dont need the backwards compatibility I'd use readtable instead.

With readtable you can specify a bunch of import options for example how to handle missing values or specify the range to be imported cf. spreadheet import options.

In case you still need to delete nan values an elegant approach would be to build a logical array with isnan() and then use this array to delete the corresponding rows/columns. Example:

AA = [magic(5) nan(5,1)];
disp(AA)
idxNanCol = all(isnan(AA));
AA(:, idxNanCol) = [];
disp(AA)

CodePudding user response：

The following code should return a matrix indicating which position of raw_values are not NaNs

index=~cellfun(@isnan,raw_values)

If you want to keep only the non NaN values:

raw_values(index)