read special cell in excel file

Hi all

This is my code. The first column in my file data is txt. Now I want when raw=2 equal raw=3 to write my station name that put in raw=1.

please help me.

a=('sabet.xlsx');
[num,txt,raw]=xlsread('a');
c1=raw(:,1);
c2=raw(:,2);
c3=raw(:,3);
for b=1:1:numel(c2)
    for c=1:1:numel(c3)
        for d=1:1:numel(c1)
   
if b == c
    
   
end
    end
end

    
end

[Mathieu NOE]

hello

it would help if you could share the excel file as well

tx

[MOzhdeh Salimi]

Hi Dear

I attached my file as txt file

Answer 1

[Mathieu NOE]

hello again

so here a small code to extract the lines where the 2 numbers in row 2 and row 3 are below a given threshold. FYI I could not find any line where the two numbers are strictly equal but there are a few ones very close. So you can decide about the threshold (limit).

here we have 3 selected names with threshold = 10 ;

% 'Anar' 40839 40830

% 'Arak' 40769 40761

% 'Baneh' 99280 99281

code is as follows : (you can expand it to save the result in an excel sheet for example or in a text file)

clc
clearvars

T = readtable('sabet.txt');
C = table2cell(T);

[m,n] = size(C);

% init
C2 = cell2num(C(:,2));
C3 = cell2num(C(:,3));
index = [];
delta = [];
threshold = 10;

% main loop
for ci = 1:m
   if  ~isnan(C2(ci)) && ~isnan(C3(ci))
        tmp = abs(C2(ci) - C3(ci)); % compute abs delta between two values (raw 2 and raw3 )
        if tmp <= threshold % keep line index of raws with delta below threshold
            index = [index; ci]; % agregate index
            delta = [delta; tmp];% agregate delta value
        end
    end
end

% plot(index,delta);

celldisp(C(index,1));
% 'Anar'	40839	40830
% 'Arak'	40769	40761
% 'Baneh'	99280	99281

[MOzhdeh Salimi]

thanks for your answer

The numbers in the third column are the station codes in the second column and I want to find them and then write the station name.

Also, code (cell2num) doesn't work.

Thanks a lot.

[Mathieu NOE]

hello

cell2num : so you are probably using an older matlab version (I have R2020b) . But you don't have problem with readtable ?

[MOzhdeh Salimi]

Yes, I am using R2018b. No this code don’t have problem. I think , if I could convert my cell data to number , my problem will be solved.

[Mathieu NOE]

ok so this is now fixed using the FEX submission that does the same thing as cell2num

cell2num - File Exchange - MATLAB Central (mathworks.com)

I simply renamed it differently so it will not conflict with the native cell2num I have;

% updated code is : 

clc
clearvars

T = readtable('sabet.txt');
C = table2cell(T);

[m,n] = size(C);

% init
% C2 = cell2num(C(:,2)); % release above R2018 ? 
% C3 = cell2num(C(:,3)); % release above R2018 ? 

C2 = another_cell2num(C(:,2)); % release below R2018
C3 = another_cell2num(C(:,3)); % release below R2018 

index = [];
delta = [];
threshold = 10;

% main loop
for ci = 1:m
   if  ~isnan(C2(ci)) && ~isnan(C3(ci))
        tmp = abs(C2(ci) - C3(ci)); % compute abs delta between two values (raw 2 and raw3 )
        if tmp <= threshold % keep line index of raws with delta below threshold
            index = [index; ci]; % agregate index
            delta = [delta; tmp];% agregate delta value
        end
    end
end

% plot(index,delta);

celldisp(C(index,1));
% 'Anar'	40839	40830
% 'Arak'	40769	40761
% 'Baneh'	99280	99281

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
function [outputmat]=another_cell2num(inputcell)
% Function to convert an all numeric cell array to a double precision array
% ********************************************
% Usage: outputmatrix=cell2num(inputcellarray)
% ********************************************
% Output matrix will have the same dimensions as the input cell array
% Non-numeric cell contest will become NaN outputs in outputmat
% This function only works for 1-2 dimensional cell arrays

if ~iscell(inputcell), error('Input cell array is not.'); end

	outputmat=zeros(size(inputcell));
	
	for c=1:size(inputcell,2)
      for r=1:size(inputcell,1)
          
	% original code      
	%     if isnumeric(inputcell{r,c})
	%       outputmat(r,c)=inputcell{r,c};
	%     else
	%       outputmat(r,c)=NaN;
	%     end
	
	%Works great if you use addition by C Schwalm to the if statement. If statement within the code should now look like :
		if isnumeric(inputcell{r,c})
		    outputmat(r,c)=inputcell{r,c};
		elseif isnumeric(str2num(char(inputcell{r,c}))) %addition
		    outputmat(r,c)=str2num(char(inputcell{r,c})); %addition
		else
		    outputmat(r,c)=NaN;
		end
	
      end  
	end

end

[MOzhdeh Salimi]

Thank you so much. But unfortunately it didn't work right.

[Mathieu NOE]

where is the trouble ? can you paste the error message ?

[MOzhdeh Salimi]

Dear Mathieu Let me explain my question again. I have excell file that includes 3 columns. First column is station name, second column is station code and third column is some station code that choose from second column. Now I want to find station name from first column that the codes equal to third column.

[Mathieu NOE]

sorry , I misunderstood the question before. now my brain has re-started to work !

check this one !

clc
clearvars

T = readtable('sabet.txt');
C = table2cell(T);

[m,n] = size(C);

% init
% C2 = cell2num(C(:,2)); % release above R2018 ? 
% C3 = cell2num(C(:,3)); % release above R2018 ? 

C2 = another_cell2num(C(:,2)); % release below R2018
C3 = another_cell2num(C(:,3)); % release below R2018 

index1 = [];
index2 = [];
delta = [];
threshold = 10;

% main loop
for ci = 1:m
   if  ~isnan(C2(ci)) && ~isnan(C3(ci))
        %tmp = abs(C2(ci) - C3(ci)); % compute abs delta between two values (raw 2 and raw3 )
        [val , ind] = min(abs(C2(ci) - C3));
        if val < 1 % keep line index of raws with delta below threshold
            index1 = [index1; ci]; % agregate index
            index2 = [index2; ind]; % agregate index
            delta = [delta; val];% agregate delta value
        end
    end
end

% plot(index,delta);

% display in command window : 
% first station code / corresponding (second) col value / second station code / corresponding (third) col value / 
[C(index1,1) C(index1,2) C(index2,1) C(index2,3)]

% ans =
% 
%   18×4 cell array
% 
%     {''Abali''             }    {[40755]}    {''Bukan''            }    {[40755]}
%     {''Abarkuh''           }    {[99539]}    {''Arak (Airport)''   }    {[99539]}
%     {''Ahar''              }    {[40704]}    {''Darrehshahr''      }    {[40704]}
%     {''Arak''              }    {[40769]}    {''Bushehr (Airport)''}    {[40769]}
%     {''Arsanjan''          }    {[99579]}    {''Dorud''            }    {[99579]}
%     {''Bam''               }    {[40854]}    {''Dehagh''           }    {[40854]}
%     {''Bandarabbas''       }    {[40875]}    {''Bushehr (Coastal)''}    {[40875]}
%     {''Bandar-E- Mahshahr''}    {[40832]}    {''Bandar-E-Deylam''  }    {[40832]}
%     {''Bandar-E-Khamir''   }    {[99674]}    {''Ahvaz''            }    {[99674]}
%     {''Bandar-E-Lengeh''   }    {[40883]}    {''Bandar-E-Dayyer''  }    {[40883]}
%     {''Biyarjomand''       }    {[40742]}    {''Eqlid''            }    {[40742]}
%     {''Damghan''           }    {[40761]}    {''Arak''             }    {[40761]}
%     {''Daregaz''           }    {[40807]}    {''Ajabshir''         }    {[40807]}
%     {''Dehloran''          }    {[40796]}    {''Abumusa Island''   }    {[40796]}
%     {''Dorud''             }    {[99444]}    {''Chahbahar''        }    {[99444]}
%     {''Dowshan Tappeh''    }    {[40753]}    {''Daran''            }    {[40753]}
%     {''Emamzadeh Jafar''   }    {[99565]}    {''Arsanjan''         }    {[99565]}
%     {''Eyvan''             }    {[99433]}    {''Bandar-E-Lengeh''  }    {[99433]}

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
function [outputmat]=another_cell2num(inputcell)
% Function to convert an all numeric cell array to a double precision array
% ********************************************
% Usage: outputmatrix=cell2num(inputcellarray)
% ********************************************
% Output matrix will have the same dimensions as the input cell array
% Non-numeric cell contest will become NaN outputs in outputmat
% This function only works for 1-2 dimensional cell arrays

if ~iscell(inputcell), error('Input cell array is not.'); end

	outputmat=zeros(size(inputcell));
	
	for c=1:size(inputcell,2)
      for r=1:size(inputcell,1)
          
	% original code      
	%     if isnumeric(inputcell{r,c})
	%       outputmat(r,c)=inputcell{r,c};
	%     else
	%       outputmat(r,c)=NaN;
	%     end
	
	%Works great if you use addition by C Schwalm to the if statement. If statement within the code should now look like :
		if isnumeric(inputcell{r,c})
		    outputmat(r,c)=inputcell{r,c};
		elseif isnumeric(str2num(char(inputcell{r,c}))) %addition
		    outputmat(r,c)=str2num(char(inputcell{r,c})); %addition
		else
		    outputmat(r,c)=NaN;
		end
	
      end  
	end

end