Cell2table issues when a numerical column is read as strings

Attached is a data file test5.mat, containing a varaible "A". It stores one column (out of many columns, which can either be numerical values or strings) that was read out of an Excel using the function "readcell". Perhaps because some of the cells of this column contain empty strings, A was read a string column.

When I tried to use the command below, everything is completely messed up.

B = cell2table(A)

What is the best way to convert this column into a numerical columns, with empty strings replaced with -999? Below is what I learned from another discussion. Unfortunately, it does not work for this case.

A(cellfun(@(x)~isscalar(x) || ismissing(x),dataValue)) = {-999};

[dpb]

I was heading down the road @Voss took when got interrupted and when came back saw his response already posted..so, let's continue down that path

load test5
C = cellfun(@class,A,'UniformOutput',false);
[uC,~,idx] = unique(C);
unique(A(idx == 1))             % what's the content of the nonnumeric cells?

ans = 3x1 cell array
    {'2'}
    {'3'}
    {'6'}

That's peculiar, indeed--the char() cells contain a numeric character that surely could have been converted -- this raises the question that one needs to see the original file in order to diagnose why the column was not all converted as numeric.

Another case where may need to do more diagnostics on the file and use the import options object to ensure that columns that are intended to be numeric are read in as numeric by setting the Vartypes field array before importing. Or, find out what is causing the input file to be created such that the values are being mistaken as char() and fix the process that creates the input file from the start.

[Leon]

Thank you for the comments.

Answer 1

[Matt J]

I would also have many columns, some of which could be made up of:

(a) all doubles

(b) a mix of empty strings (e.g., ' ', ' ', etc.) and doubles (the case I described above),

(c) a mix of strings and doubles.

(d) all strings,

It would be better if you were to provide a more complete example test6.mat with all these cases, but I think this will work:

load test5

map1=cellfun('isclass',A,'double'); 
map2=cellfun(@(x)ischar(x)&&isempty(deblank(x)),A);

tf=all(map1|map2,1),

tf = logical
   0

A(tf&map2)={-999};
A(:,~tf)=cellstr(string(A(:,~tf)));

B=cell2table(A)

B = 2087x1 table
      A  
    _____

    {'2'}
    {'2'}
    {'2'}
    {'2'}
    {'2'}
    {'2'}
    {'2'}
    {'2'}
    {'2'}
    {'3'}
    {'2'}
    {'2'}
    {'2'}
    {'2'}
    {'2'}
    {'2'}

[Leon]

Many thanks for this even simplier solution.

Please see my comments above. It would be great if either of you could help me solve the broader issue. Many thanks.

[Matt J]

I have incorporated your comments.

[Leon]

Wow, this is amazingly done. It works really well. Thank you so much, Matt! Many thanks to all those who contributed to this thread!