I have an Excel sheet with headers of different depths and the numbers are not being read (ex.: d=-12.82m is being read as x_12_82M),

I am trying to convert from Excel to a different format, for different depths but the headers are not being read properly.

I even tried changing it manually in Excel but if I do, they will be read as part of the data and not as headers

[Stephen23]

Please upload a sample spreadsheet by clicking the paperclip button.

Answer 1

[Chunru]

% The first row is treated as var names.  Use preserve to keep the original
% format
a = readtable("https://www.mathworks.com/matlabcentral/answers/uploaded_files/1544212/Sample.xlsx", "VariableNamingRule", "preserve")

a = 4×41 table
           Datum            -12.82 m    -12.47 m    -12.12 m    -11.77 m    -11.42 m    -11.07 m    -10.72 m    -10.37 m    -10.02 m    -9.67 m    -9.32 m    -8.97 m    -8.62 m    -8.27 m    -7.92 m    -7.57 m    -7.22 m    -6.87 m    -6.52 m    -6.17 m    -5.82 m    -5.47 m    -5.12 m    -4.77 m    -4.42 m    -4.07 m    -3.72 m    -3.37 m    -3.02 m    -2.67 m    -2.32 m    -1.97 m    -1.63 m    -1.28 m    -0.93 m    -0.58 m    -0.23 m    0.12 m    0.47 m    0.82 m
    ____________________    ________    ________    ________    ________    ________    ________    ________    ________    ________    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    ______    ______    ______

    27-Jun-2019 12:00:00      0.62        0.64        0.65        0.66        0.66        0.72        0.74        0.76        0.73       0.75       0.77       0.78       0.79       0.86       0.83       0.82       0.88       0.87       0.91       0.91       0.96       0.99       0.99       0.98       0.99          1       1.01       1.08       1.05       0.87       0.55        NaN        NaN        NaN        NaN        NaN        NaN       NaN       NaN       NaN  
    27-Jun-2019 12:01:00      0.55        0.62        0.67        0.68        0.66        0.71        0.72        0.75        0.75       0.79       0.82       0.82       0.82       0.81       0.81       0.84       0.86        0.9       0.91       0.92       0.91       0.93       0.95       0.98       0.99       0.99          1       0.98          1       0.98       0.86        NaN        NaN        NaN        NaN        NaN        NaN       NaN       NaN       NaN  
    27-Jun-2019 12:02:00      0.62        0.62        0.65        0.66        0.67        0.73        0.72        0.76        0.75       0.76       0.77       0.79       0.78       0.79        0.8       0.85       0.89       0.87       0.91       0.91       0.93       0.95       0.97          1       1.01       1.05       1.06       1.06       1.04       1.03       0.89        NaN        NaN        NaN        NaN        NaN        NaN       NaN       NaN       NaN  
    27-Jun-2019 12:03:00      0.67        0.71        0.76        0.78         0.8        0.79        0.78         0.8        0.75       0.76       0.75       0.75        0.8       0.82       0.82       0.84       0.88       0.89       0.89       0.93       0.97       0.99       1.02       1.02       1.02       1.05       1.03       1.07       1.02       0.92       0.93        NaN        NaN        NaN        NaN        NaN        NaN       NaN       NaN       NaN  

s = a.Properties.VariableNames;  % get the variable names
whos

  Name        Size            Bytes  Class    Attributes

  a           4x41            11189  table              
  cmdout      1x33               66  char               
  s           1x41             4846  cell               

d = cellfun(@(x) sscanf(x, "%f"), s(2:end) )  % convert to depth

d = 1×40

-12.8200 -12.4700 -12.1200 -11.7700 -11.4200 -11.0700 -10.7200 -10.3700 -10.0200 -9.6700 -9.3200 -8.9700 -8.6200 -8.2700 -7.9200 -7.5700 -7.2200 -6.8700 -6.5200 -6.1700 -5.8200 -5.4700 -5.1200 -4.7700 -4.4200 -4.0700 -3.7200 -3.3700 -3.0200 -2.6700

[janas]

Thank you, that worked