Removing zeros in excel

Hi Guys,

Does anyone know how to remove zeros while keeping the zero that is at the beginning and end of a value from excel sheet to make figure 1 to look like figure 2.

Thank you!

[Walter Roberson]

You need to describe why those particular 0 0 should be preserved, what rule to use. The rest is easy .

[Guillaume]

Also, if you don't know how to do that in matlab, why do you want to do it in matlab? You may as well write the code in VBA and do it all in excel. It would be faster

[LIM JIAXIN]

those particular 0 show in figure 2 is preserved for plotting the figure in 3D. Is there any ways to preserve those 0 that is at the beginning and end of the column value while removing the rest of the unwanted 0. I am quite new to this, thank you.

[LIM JIAXIN]

@guillaume the reason i'm doing this in matlab is i have some knowledge compare to VBA (0 knowledge). Would appreciate if i can get some help here instead.

[Walter Roberson]

II am not clear on why the 0 is not preserved on top of the column with 4s?

[Bob Thompson]

There are two 0s on top of the 4s, only the adjacent one is retained.

At least that is what it looks like to me.

Answer 1

[Guillaume]

No loop, no cellfun:

filetoedit = 'C:\somewhere\somefile.xlsx';

[data, ~, ascell] = xlsread(filetoedit);
ascell(~conv2(data ~= 0, [1; 1; 1], 'same')) = {[]};  %the conv2(data ~= 0, [1; 1; 1], 'same') expands non-zeros one row up and down. The remaining 0 are replaced by []
xlswrite(filetoedit, ascell);

Answer 2

[Bob Thompson]

Guillaume and Walter will probably have much better methods for doing this, but here is my first thought.

[num,txt,data] = xlsread('myfile.xlsx'); % Read the excel file
for i = 1:size(data,1); % Loop through rows
    for j = 1:size(data,2); % Loop through columns
        if i > 1 & i < size(data,1) & data{i,j} == 0 & data{i+1,j} == 0 & data{i-1,j} == 0
            data{i,j} = [];
        elseif i == 1 & data{i,j} == 0 & data{i+1,j} == 0
             data{i,j} = [];
        elseif i == size(data,1) & data{i,j} == 0 & data{i-1,j} == 0
             data{i,j} = [];
        end
    end
end

Unfortunately, I wasn't able to find a method that didn't involve loops, but I'm also not very good with cellfun.

[Guillaume]

cellfun is just a loop in disguise, often slower. What you gain with cellfun is guarantee that the output is the correct size and clarity (in my opinion) of the code. cellfun is also an example of functional programming which may be preferred over the imperative nature of loops.

Neither loops, nor cellfun are needed for this however.