Cell array element comparison

Hi everybody,

i have a big excel table full of strings. For further operations i want to convert all strings to a number by a translation table.

For example my raw table (cell array) out of the excel worksheet:

1  'MG1'  'GD8'  'AR1'  'MP6'  3665   72,4
2  'MG3'  'G43'  'AR2'  'MP5'  3665   72
3  'MG2'  'G42'  'AR1'  'MA5'  4330   74
4  'MG5'  'GF1'  'AR3'  'MA5'  4330    73,5
5  'MG3'  'GD8'  'AR1'  'MP6'  2990    71
6       'MG2'  'GD8'  'AR1'  'MP6'  3665  73

Now i want to transfer this table into an numeric table by this translation table, e.g.

'MG1'  1
'MG2'  2
'MG3'  3 ...

to such a table:

1  1  1  1  1  3665   72,4
2  3  2  2  2  3665   72
3  2  3  1  3  4330   74
4  4  4  3  3  4330  73,5
5  3  1  1  1  2990    71
6       2  1  1  1  3665  73

The way I wanted to do is by this:

[num,text,raw]=xlsread('TestTable.xlsx')
for i=1:6
    for j=2:5
      if (raw(i,j)=={'GD1'})
          raw(i,j)=1;
          %and so on
      end
    end
end

But the comparison of the cell array elements doesn't work. Is there maybe an even better way? Switch/case doesn't work with cell array elements i guess...

[per isakson]

It might be easier to do something like this

>> str = 'MG2';
>> % ignore two characters and convert the rest to numeric
>> sscanf( str, '%*2c%f' ) 
ans =
     2

Use strcmp rather than == to compare strings

Answer 1

[Image Analyst]

Simply use readtable():

t = readtable('TestTable.xlsx')
% Extract column #2, then column #1 into a new table, t2column:
t2column = t(:[2,1]);

I didn't test it but I think it should work.