Convert nonuniform cell array to numeric array

Question

I am using xlsread in MATLAB to read in sheets from an excel file. My goal is to have each column of the excel sheet read as a numeric array. One of the columns has a mix of numbers and numbers+char. For example, the values could be 200, 300A, 450, 500A, 200A, 100. here is what I have so far:

[num, txt, raw] = xlsread(fileIn, sheets{ii});    % Reading in each sheet from a for loop
myCol = raw(:, 4)                             % I want all rows of column 4
for kk=1:numel(myCol)
       if iscellstr(myCol(kk))
           myCol(kk) = (cellfun(@(x)strrep(x, 'A', ''), myCol(kk), 'UniformOutput', false));
       end
end

myCol = cell2mat(myCol);

This is able to strip off the char from the number but then I am left with:

myCol = 

[200]

'300'

[450]

'500'

'200'

[100]

which errors out on cell2mat with:

cell2mat(myCol)

??? Error using ==> cell2mat at 46

All contents of the input cell array must be of the same data type.

I feel like I am probably mixing up () and {} somewhere. Can someone help me out with this?

Answer 1

Let me start from reading the file

[num, txt, raw] = xlsread('test.xlsx');
myCol = raw(:, 4);

idx = cellfun(@ischar,myCol ); %# find strings
data = zeros(size(myCol)); %# preallocate matrix for numeric data
data(~idx) = cell2mat(myCol(~idx)); %# convert numeric data
data(idx) = str2double(regexprep(myCol(idx),'\D','')); %# remove non-digits and convert to numeric

Answer 2

The variable myCol is initially a cell array containing both numbers and strings, something like this in your example:

myCol = {200; '300A'; 450; '500A'; '200A'; 100};

The steps you have to follow to convert the string entries into numeric values is:

1. Identify the cell entries in myCol that are strings. You can use a loop to do this, as in your example, or you can use the function CELLFUN to get a logical index like so:

   index = cellfun(@ischar,myCol);
   

2. Remove the letters. If you know the letters to remove will always be 'A', as in your example, you can use a simple function like STRREP on all of your indexed cells like so:

   strrep(myCol(index),'A','')
   

   If you can have all sorts of other characters and letters in the string, then a function like REGEXPREP may work better for you. For your example, you could do this:

   regexprep(myCol(index),'\D','')
   

3. Convert the strings of numbers to numeric values. You can do this for all of your indexed cells using the function STR2DOUBLE:

   str2double(regexprep(myCol(index),'\D',''))
   

The final result of the above can then be combined with the original numeric values in myCol. Putting it all together, you get the following:

>> index = cellfun(@ischar,myCol);
>> result(index,1) = str2double(regexprep(myCol(index),'\D',''));
>> result(~index) = [myCol{~index}]

result =

   200
   300
   450
   500
   200
   100