importing merged cells of xls file to MATLAB

1
votes

I have some xls files, which they all have merged cells in specific columns. so when I use xlsread, the data of that column is read as NaN. for example when my data is:

1   2   2   5
3   1   2   1
2   4   2   3

if the last column be the merging result of 4th and 5th columns, when I use xlsread the output will be:

1   2   2   NaN
3   1   2   NaN
2   4   2   NaN

For my real data, it is time consuming to unmerge columns in excel before importing. how can I import them to MATLAB in the merged format?

1
excelmatlabimportmerge
This question may be better suited to Super User or mathematica.stackexchange.com/search?q=matlab – ashleedawg
I think that group is dealing the problems only regarding mathematica @ashleedawg – parisa
The merged cells are being read as strings not at numeric data. You'll find them in the 2nd output of xlsread. You may also be interested in the 3rd output, which contains both numeric and string data. – Phil Goddard
@parisa - did you click the link and see the 900 matlab posts including a few almost identical to yours? Or, according to this and other meta discussions, a better place for this type of question is SuperUser (700 questions just at that link). More importantly, have you read this @ help center? Which category is "How to import numbers into Matlab?": a specific programming problem, a software algorithm, or a commonly used programmer's tool? Is it a problem unique to software development? – ashleedawg
@ashleedawg, if this question isn't suitable here then neither are a large majority of the questions with the MATLAB tag. IMHO it is suitable here, although could be improved by showing some code. None the less, the question is pretty clear to anyone who's loaded spreadsheet data into MATLAB. – Phil Goddard

1 Answers

0
votes

I've tested saving some Excel 2013 sheets with numbers in merged cells, in either .xlsx or .xls format, and then reading them into MATLAB R2016b with xlsread and the numbers are read correctly. So either your version(s) behave differently or, as noted in Phil Goddard's comment, the values in the merged cells are formatted as text in Excel.

As Phil says, if you use the syntax

[num, txt, both] = xlsread('myfile.xls')

the result will be a numeric matrix num containing only the cell values that are numbers, a cell array txt containing only the cell values that are text, and a cell array both containing the values of all the cells as either numbers or character vectors as appropriate. If you have columns or rows containing mixed numbers and text, the text cells will return NaN values in num.