Converting cell array that contains numbers and characters to double with numbers and NaN for characters

After pulling data from an Excel file, my script first scans the title row and then finds the specific column number with a certain title and then takes that colomn and converts it to a double using cell2mat. However, when a colomn contains a space/words instead of NaN, I can't use the cell2mat function. This is my code that works if all the values are numbers or blanks:

[ndata, text, alldata] = xlsread('Data.xlsx');

titles = alldata(1,:);

Col_num_C = find(titles == "title") %Column number with title
CMA = cell2mat(alldata(2:end,Col_num_C))

[alex_h]

One way I could think to solve this is by matching the 'titles' number to the column number in my ndata column number but I am not sure how to do that because MATLAB basically deletes all text columns in the ndata double when it reads the excel file.

[Jess Lovering]

Have you considered reading the information in with importdata instead? You may already know this, but you can go through the Import Data Gui and it will download your data as column vectors if select that under output type. At the top of the rows you can select if you want the data to be numbers and it will replace all non-numeric values with NaN. You can even have the GUI generate the script for you under the Import Selection tab.

Answer 1

[Guillaume]

First, use readtable instead of xlsread. It will automatically detect that the first row is a header and use that to name the variables of the table. This will simplify the code for obtaining the title column to just:

alldata = readtable('Data.xlsx');  %should be all that is needed but if your file is strangely formatted readtable may need some extra options
CMA = alldata.title;

Now if that title column is a cell array of numbers and text, then

CMA = str2double(alldata.title);

is probably what you're after. If not, attach an example file so we understand exactly what the input is.

You could also try

alldata = convertvars(alldata, 'title', 'double');

to keep it all in the table.