Effective way to convert/create matrix from mixed cell/string

I have a cell array like the one below and I need a fast and effective way to 1) remove the empty columns, 2) convert the cells containing a string with "#" to the number after the "#" (6.504), and finally 3) create or convert the whole cell array to a data matrix like "data" below. Is there a smart way to do all this? Sometimes there might be more that one string located somewhere else, so I need a way to find everyone in the cell array. Any suggestions are highly appreciated.

array ={ 
    [47.4500]    ''    [23.9530]    ''    [12.4590]
    [34.1540]    ''    [15.1730]    ''    [ 9.6840]
    [45.2510]    ''    [23.3770]    ''    [13.0670]
    [29.9350]    ''    [14.8680]    ''    '# 6.504'}

data =[
    47.4500   23.9530   12.4590
    34.1540   15.1730    9.6840
    45.2510   23.3770   13.0670
    29.9350   14.8680    6.5040]

Answer 1

[Rik]

array ={
    [47.4500]    ''    [23.9530]    ''    [12.4590]
    [34.1540]    ''    [15.1730]    ''    [ 9.6840]
    [45.2510]    ''    [23.3770]    ''    [13.0670]
    [29.9350]    ''    [14.8680]    ''    '# 6.504'};
array=cellfun(@convert_to_value,array,'UniformOutput',false);
data=cell2mat(array);

function out=convert_to_value(in)
if ischar(in) && ~isempty(in) && strcmp(in(1),'#')
    out=str2double(in(2:end));
elseif isempty(in)
    out=[];
else
    out=in;
end
end

[JB]

Hi Rik, thanks a lot for your suggestion, however I get the following error message:

Error: File: testscript.m Line: 8 Column: 1
Function definitions are not permitted in this context.

[Rik]

Then your version of Matlab does not support functions in scripts. The solution to this is to save the function in a separate file, or make the top part a function as well.

[JB]

I am using matlab b2015b. I will try to take the function out into a seperate file.

[JB]

PERFECT.. Thanks a lot Rik