Convert nonuniform cell array to numeric array

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

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