MATLAB Answers


using xlsread, i want to bifurcate data in different columns separated by commmas in each row of a excel sheet.

Asked by Anupam Agarwal on 25 Apr 2018
Latest activity Answered by Walter Roberson
on 26 Apr 2018
I have a excel sheet containing author and their respective book titles separated by comma in each row. I want to separate the data as author column and title column using MATLAB. Can anybody help me please?


Please attach your xls-file (his small part).
Here it is ..... I want to separate the author name, book title and publisher in separate columns. I am unable to access the content of each row. My logic is to scan the comma and separate the content before and after comma.

Sign in to comment.

1 Answer

Answer by Walter Roberson
on 26 Apr 2018

[~, txt, ~] = xlsread(...);
split_fields = regexp(txt(:,1), ',', 'split');
split_fields will now be a cell array containing as many entries as there were text lines in the first column. Each of the entries will be a cell array of character vectors. There will be a different number of character vectors for each original row.
You are going to have difficulty figuring out which of the portions mean on any one row. We can see from your samples that the patterns include:
  • name, title, publisher, city
  • name, title, edition, publisher
  • name, title, city
  • name, title, publisher
  • name, name, title, publisher
The "name, title, city" line is arguably a "name, title, publisher" line, but there are multiple publishers in that city, with "Oxford University Press" only being the best known of them; we would have to assume that an abbreviated publisher name took precedence over a non-abbreviated city.
You got lucky in your samples that none of the titles happened to include commas: you should assume that in your larger dataset that commas in titles will occur.


Sign in to comment.