Convert a Cell array with char and white spaces to Cell array with double without white spaces

The colums in the timetable consist of cell arrays with characters. These charachters consist of a whit space after the numbers. The figure above is for clarification.

I want to change the cell arrays with charachters and whitspaces to a cell array with doubles(only numbers) without the white spaces.

I've already tried several things as example the script below. But C only gives NaN.

A = string(weatherdata.Temperature__F);

B = deblank(A);

C = str2double(B);

Does anyone have an idea how I can solve this? Thank you very much in advance.

[Stephen23]

"Does anyone have an idea how I can solve this?"

Much better to fix this problem at the cause (i.e. when the data is imported), not later by messing around with text.

Please upload a sample file by clicking on the paperclip button.

[Dora de Jong]

Thank you for your reaction. The data is a csv file. I added it with the paperclip

[Stephen23]

"The data is a csv file."

A .CSV is a simple text file whose format is defined by convention more than anything else.

What you actually uploaded is an .XLSX file, i.e. a rather complex XML-based proprietary spreadsheet file.

Nine columns of the "numeric" data are actually stored as text (replete with trailing non-breaking space character), which is the cause of your problems. Rather than messing around with trying to fix this in MATLAB, a much better solution would be to fix this badly formatted data when the spreadsheet is created.

Answer 1

[Walter Roberson]

filename = 'https://www.mathworks.com/matlabcentral/answers/uploaded_files/750699/sample%20data.xlsx';
opt = detectImportOptions(filename, 'VariableNamingRule', 'preserve');
opt = setvartype(opt, 1, 'datetime');
opt = setvartype(opt, 6, 'categorical');
t = readtable(filename, opt);
t.Properties.VariableNames{1} = 'Date';
badcols = [3:5 7:11 13];
varnames = t.Properties.VariableNames;
for col = badcols
    thisvar = varnames{col};
    t.(thisvar) = str2double(regexprep(t.(thisvar), '\s+', '', 'once'));
end
t.Date = t.Date + days(t.Time);
t.Date.Format = 'MMM d, yyyy HH:mm:ss';
tt = table2timetable(t)

tt = 4×12 timetable
            Date               Time       Temperature_F    DewPoint_F    Humidity_percentage    Wind    Speed_mph    Gust_mph    Pressure_in    Precip. Rate._in    Precip. Accum._in    UV    Solar_w/m²
    _____________________    _________    _____________    __________    ___________________    ____    _________    ________    ___________    ________________    _________________    __    __________

    Sep 14, 2021 00:04:00    0.0027778        57.2            54.7               91             West       0.2         0.4          29.76              0                    0            0         0     
    Sep 14, 2021 00:09:00      0.00625        57.1            54.5               91             NW         0.1         0.3          29.76              0                    0            0         0     
    Sep 14, 2021 00:14:00    0.0097222        56.8            54.4               91             WNW        0.2         0.3          29.76              0                    0            0         0     
    Sep 14, 2021 00:19:00     0.013194        56.9            54.6               92             West       0.6         0.9          29.76              0                    0            0         0     

[Dora de Jong]

Thank you very much for this solution!!