How can I split up several numbers in one cell from an excel file?

Dear Matlab-Community,

I want to import an excel file with 9 numbers in one cell separated by a comma. I need this data to be split up and written in 9 columns.

For example:

Input:

0,565.24,18.3882,0,69.1582,0,15.68,0,0

1,582.69,18.3895,0,64.1595,0,15.33,0,0

... and so on.

Could somebody help me with this?

Thanks in advance!

Domi

Answer 1

[Star Strider]

I am not exactly certain what you want.

One option would be the mat2cell (link) function. Each column would be its own cell, so you could then address them easily. Use the first output of xlsread (it should be a numeric matrix).

The code would be something like this:

[D,S] = xlsread('YourFile.xlsx');
ColCells = mat2cell(D, size(D,1), ones(1,size(D,2)));

[Star Strider]

@Dominik Kümpflein —

You did not say what the format of your file is, or what the extension is. I assumed it is a ‘.xlsx’ file originally.

From your description of it, it may be a ‘.csv’ file instead. (Excel can create and read both types.) If so, they your problem is considerably simplified, because the commas are delimiters, and when you read it, the values will be parsed into their respective columns.

My code then becomes:

D = xlsread('YourFile.csv');
ColCells = mat2cell(D, size(D,1), ones(1,size(D,2)));

It may be easier to copy it as a ‘.txt’ file, then read the text file with the csvread function. You could then use the second line of my code to parse it into individual cell columns.

[Dominik Kümpflein]

Yes you are right. It's an csv-file. I'm not used to that and didn't know how to handle it so I converted it to an .xlsx, sorry for that. (Not gonna do that mistake again)

I tried your suggestion with xlsread the csv-file, but the mat D remains empty and so 'ColCells' of course too. But the way with the txt file works perfectly!

Thank you so much!

[Star Strider]

As always, my pleasure!

Answer 2

[Dominik Kümpflein]

First of all, thanks for your quick answers.

I'm sorry if I didn't made clear enough what I wanna do.

I added a Screenshot of how my Excel-File looks. These are data logged by a GoPro that i extracted from a MP4-File. To do further calculations i need to extract the GPS-Data from it.

Unfortunatly, all data from one timestep is written in one Excel-Cell and separated by a comma. So now I'm looking for a way to read the data in Matlab and split it up, so i get a mat or cell-array with one column for the timestep, one for the Latitude one for the Longitude and so on.

[madhan ravi]

Try reading the file using readtable()

[Dominik Kümpflein]

The output with readtable is still the same. All numbers in one column.

[madhan ravi]

Attach your data.

[Dominik Kümpflein]

Here is my Excel file for the input

Answer 3

[Bob Thompson]

[~,~,data] = xlsread('myexcel.xlsx',range);
data = cellfun(@(x) str2num(x),data);

This is a first crack at the code. I'm not absolutely certain that the cellfun will work as I want it to, since I haven't tested it. Basically, you are going to read all of the cells of the excel file into a matlab cell array. I expect that the cells with multiple numbers will be treated as a string, but using str2num should convert them into actual number arrays for you.