MATLAB Answers

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

3 views (last 30 days)
alex_h on 6 Feb 2020
Edited: Guillaume on 6 Feb 2020
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 on 6 Feb 2020
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
Jess Lovering on 6 Feb 2020
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.

Sign in to comment.

Answers (1)

Guillaume on 6 Feb 2020
Edited: Guillaume on 6 Feb 2020
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.


Sign in to comment.

Sign in to answer this question.