subdivide numbers inside a file .xlsx

How can I divide the numbers into different columns in this attached .xlsx file?

filename = 'file.xlsx';
t = readtable(filename);

Warning: Column headers from the file were modified to make them valid MATLAB identifiers before creating variable names for the table. The original column headers are saved in the VariableDescriptions property.
Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.

t(1:6,:)

ans = 6x1 table
                                          x_Name_                                       
    ____________________________________________________________________________________

    {'File 1'                                                                          }
    {0x0 char                                                                          }
    {'[Data]'                                                                          }
    {'X [ m ], Y [ m ], Z [ m ], Number on File 1, Velocity [ m s^-1 ]'                }
    {'-3.55882719e-02, -1.09321419e-02, 8.20557680e-03, 0.00000000e+00, 1.00000292e-01'}
    {'-3.59198786e-02, -1.09023182e-02, 8.16907175e-03, 1.00000000e+00, 1.00000285e-01'}

Answer 1

[Binaya]

Hi Alberto

You can follow the below steps to divide the numbers into different columns:

1. Read the data using "readcell" function.

data = readcell('file.xlsx');

2. Select the range of data where you have the numbers present.

data = data(6:13)

data = 8x1 cell array
    {'-3.55882719e-02, -1.09321419e-02, 8.20557680e-03, 0.00000000e+00, 1.00000292e-01'}
    {'-3.59198786e-02, -1.09023182e-02, 8.16907175e-03, 1.00000000e+00, 1.00000285e-01'}
    {'-3.57956178e-02, -1.06404135e-02, 8.25155806e-03, 2.00000000e+00, 1.00000285e-01'}
    {'-3.54897231e-02, -1.05726253e-02, 8.30925070e-03, 3.00000000e+00, 1.00000285e-01'}
    {'-3.52230370e-02, -1.06995245e-02, 8.31269473e-03, 4.00000000e+00, 1.00000285e-01'}
    {'-3.53128649e-02, -1.10662561e-02, 8.20836797e-03, 5.00000000e+00, 1.00000277e-01'}
    {'-3.56706455e-02, -1.12167289e-02, 8.12293869e-03, 6.00000000e+00, 1.00000277e-01'}
    {'-3.59513536e-02, -1.11927493e-02, 8.09170678e-03, 7.00000000e+00, 1.00000285e-01'}

3. Split the string in each row at the comma and convert the strings to numbers. Iterate this over all the rows and store each row in an array.

numRows = length(data);
for i = 1:numRows    
    newData(i, :) = str2double(strsplit(data{i}, ','));
end
newData

newData = 8x5

-0.0356 -0.0109 0.0082 0 0.1000 -0.0359 -0.0109 0.0082 1.0000 0.1000 -0.0358 -0.0106 0.0083 2.0000 0.1000 -0.0355 -0.0106 0.0083 3.0000 0.1000 -0.0352 -0.0107 0.0083 4.0000 0.1000 -0.0353 -0.0111 0.0082 5.0000 0.1000 -0.0357 -0.0112 0.0081 6.0000 0.1000 -0.0360 -0.0112 0.0081 7.0000 0.1000

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

4. You can now use the newData in your code or save it in a new XLSX file using the "writetable" function.

table = array2table(newData)

table = 8x5 table
    newData1     newData2     newData3     newData4    newData5
    _________    _________    _________    ________    ________

    -0.035588    -0.010932    0.0082056       0          0.1   
     -0.03592    -0.010902    0.0081691       1          0.1   
    -0.035796     -0.01064    0.0082516       2          0.1   
     -0.03549    -0.010573    0.0083093       3          0.1   
    -0.035223      -0.0107    0.0083127       4          0.1   
    -0.035313    -0.011066    0.0082084       5          0.1   
    -0.035671    -0.011217    0.0081229       6          0.1   
    -0.035951    -0.011193    0.0080917       7          0.1   

writetable(table,'newFile.xlsx')

Here are the documentation links to the functions used above:

1. readcell: https://www.mathworks.com/help/matlab/ref/readcell.html
2. strsplit: https://www.mathworks.com/help/matlab/ref/strsplit.html
3. writetable: https://www.mathworks.com/help/matlab/ref/writetable.html

I hope this answers your query.

Answer 2

[Stephen23]

Simpler:

C = readcell('file.xlsx');
M = str2double(split(C(6:end),', '))

M = 8x5

-0.0356 -0.0109 0.0082 0 0.1000 -0.0359 -0.0109 0.0082 1.0000 0.1000 -0.0358 -0.0106 0.0083 2.0000 0.1000 -0.0355 -0.0106 0.0083 3.0000 0.1000 -0.0352 -0.0107 0.0083 4.0000 0.1000 -0.0353 -0.0111 0.0082 5.0000 0.1000 -0.0357 -0.0112 0.0081 6.0000 0.1000 -0.0360 -0.0112 0.0081 7.0000 0.1000

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

T = array2table(M, 'VariableNames',split(C(5),', '))

T = 8x5 table
     X [ m ]      Y [ m ]      Z [ m ]     Number on File 1    Velocity [ m s^-1 ]
    _________    _________    _________    ________________    ___________________

    -0.035588    -0.010932    0.0082056           0                    0.1        
     -0.03592    -0.010902    0.0081691           1                    0.1        
    -0.035796     -0.01064    0.0082516           2                    0.1        
     -0.03549    -0.010573    0.0083093           3                    0.1        
    -0.035223      -0.0107    0.0083127           4                    0.1        
    -0.035313    -0.011066    0.0082084           5                    0.1        
    -0.035671    -0.011217    0.0081229           6                    0.1        
    -0.035951    -0.011193    0.0080917           7                    0.1        

Answer 3

[Walter Roberson]

Your data is stored in single cells of the xlsx. For example

-3.55882719e-02, -1.09321419e-02, 8.20557680e-03, 0.00000000e+00, 1.00000292e-01

is stored all as a single cell, not as seperate cells.

format long g
filename = 'file.xlsx';
temp = readcell(filename, 'headerlines', 5);
t = cell2mat(cellfun(@str2num, temp, 'uniform', 0))

t = 8x5

-0.0355882719 -0.0109321419 0.0082055768 0 0.100000292 -0.0359198786 -0.0109023182 0.00816907175 1 0.100000285 -0.0357956178 -0.0106404135 0.00825155806 2 0.100000285 -0.0354897231 -0.0105726253 0.0083092507 3 0.100000285 -0.035223037 -0.0106995245 0.00831269473 4 0.100000285 -0.0353128649 -0.0110662561 0.00820836797 5 0.100000277 -0.0356706455 -0.0112167289 0.00812293869 6 0.100000277 -0.0359513536 -0.0111927493 0.00809170678 7 0.100000285

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>