Operate a string in a cell matrix

Hi, I have a cell matrix imported from excel:

    [39]    [1232]    [          567]    [          NaN]
    [40]    [  48]    [           41]    [          949]
    [41]    [  48]    [         1686]    [           40]
    [42]    [ 754]    [          753]    [           40]
    [43]    [  48]    '800 1069 1152'    [          983]
    [44]    [  43]    [           48]    '700 109   152'
    [45]    [  52]    [         1340]    [         1363]
    [46]    [1340]    [           45]    [         1594]

there are all number with each taking one gird in excel except two "800 1069 1152" and '700 109 152' taking a single grid. In matlab, by [num,txt,raw] = xlsread() the raw matrix is a cell matrix with '800 1069 1152' a string. Is there a way I want to just keep 800, and 700 from those two strings, and convert this cell matrix to a normal matrix for future programming?

Thank you for any suggestion Kyle

Answer 1

[Azzi Abdelmalek]

 A={[39]    [1232]    [          567]    [          NaN]
    [40]    [  48]    [           41]    [          949]
    [41]    [  48]    [         1686]    [           40]
    [42]    [ 754]    [          753]    [           40]
    [43]    [  48]    '800 1069 1152'    [          983]
    [44]    [  43]    [           48]    '700 109   152'
    [45]    [  52]    [         1340]    [         1363]
    [46]    [1340]    [           45]    [         1594]}

r=cellfun(@(x) isstr(x),A)
q=cellfun(@(x) regexp(x,' ','split'),A(r),'uni',false)
A(find(r==1))=num2cell(cellfun(@(x) str2num(x{1}),q))
out=cell2mat(A)

[Kyle]

thank you Azzi

[Jan]

Some simplifications:

r    = cellfun('isclass', A, 'char');
A(r) = cellfun(@(x) sscanf(x, '%g', 1), A(r), 'uni', false);
out  = cell2mat(A);

Answer 2

[Muthu Annamalai]

Hello Kyle,

Solution is fairly straightforward.

First you can identify the locations where '*xlsread*' bailed out, by looking for the NaN in the matrix p,

      [p,q,r]=xlsread('xl_file_name.xls')
      [R,C]=find(isnan(p))

and then work to extract the first element, which is what you mention in the post, from 'cell-string' of numbers to a 'double' type. You can do this by, working your way through the row-column index

for idx = 1:length(R)
     long_str = r{R(idx),C(idx)};% pickup the long string
     t=regexp(long_str,' ','split'); %split it into n-element strings
     r{R(idx),C(idx)}=str2double(t{1});% convert to double
end

Finally the magic step is to convert your cell-matrix into a regular matrix,

p = cell2mat(r);

Together your code should look like,

[p,q,r]=xlsread('xl_file.xls')
[R,C]=find(isnan(p))

for idx = 1:length(R)
    long_str = r{R(idx),C(idx)};% pickup the long string
    t=regexp(long_str,' ','split'); %split it into n-element strings
    r{R(idx),C(idx)}=str2double(t{1});% convert to double
end

p = cell2mat(r)

HTH, -Muthu

[Kyle]

Thank you Muthu