Importing Microsoft Excel data to MATLAB as a single variable from multiple spreadsheets that contains various data ranges

I have 6 spreadsheets containing station locations from which I want to generate a single variable in a cell array called "Station" (it will be a 251x1 cell array). The names of the 6 sheets are seen below in 'opts.Sheet' and the ranges that I want from each of the spreadsheets are specified below in 'ranges'. I'm wondering how I can loop through each of the spreadsheets and extract the data for the ranges I have specified? The below code was generated from the MATLAB import tool and I'm simply trying to modify it. If there is a more functional way of solving this problem any suggestions would be appreciated.

%% Setup the Import Options
opts = spreadsheetImportOptions("NumVariables", 1);

% Specify sheet
opts.Sheet = "CTD 2014"; %"CTD 2015"; "CTD 2016"; "CTD 2017"; "CTD 2018"; "CTD 2019";

% Specify column names and types
opts.VariableNames = "Station";
opts.VariableTypes = "char";
opts = setvaropts(opts, 1, "WhitespaceRule", "preserve");
opts = setvaropts(opts, 1, "EmptyFieldRule", "auto");

% Import the data
tbl = table;
%                CTD 2014                           CTD 2015                               CTD 2016                       CTD 2017 ..... etc
ranges = ["D5:D7" "D9:D25" "D31:D39"]; %["D5:D31" "D33:D51" "D53:D53" "D55:D58"]; ["D6:D25" "D27:D36" "D38:D46"]; ["D5:D11" "D13:D14" "D16:D25" "D27:D71"]; ["D5:D12" "D14:D19" "D21:D43" "D46:D47"]; ["D5:D8" "D10:D15" "D29:D47"];

for idx = 1:length(ranges)
    opts.DataRange = ranges(idx);
    tb = readtable("SBE 19plus CTD Profiling Database.xlsx", opts, "UseExcel", false);
    tbl = [tbl; tb]; %#ok<AGROW>
end

%% Convert to output type
Station = tbl.Station;

%% Clear temporary variables
clear idx opts ranges tb tbl

[darova]

Where is Excel file?

[Jalaj Gambhir]

Can you attach the excel file?

[Peter Alexander Mott]

my apologies. Excel file now attached

Answer 1

[Amit]

Hello Peter, Try the below solution :

station = [];
 SheetName = {'CTD 2014'; 'CTD 2015'; 'CTD 2016'; 'CTD 2017'; 'CTD 2018'; 'CTD 2019'};
FileName = 'SBE 19plus CTD Profiling Database.xlsx';
for sheet_idx = 1:length(SheetName)
    opts = detectImportOptions(FileName,'Sheet',SheetName{sheet_idx});
    [station_raw] = readtable(FileName,opts);
    station_raw = station_raw.Station;
    station_raw(strcmp(station_raw,'-')) = [];
    station_raw(strcmp(station_raw,'?')) = [];
    station_raw = station_raw(~cellfun(@isempty, station_raw));
    station = [station; station_raw];
end

I hope this helps...!