find strings in excel on different sheets

12 views (last 30 days)
basically, this finds a user inputted string, and returnes the row in which that string is found.
this is the code I adapted from there:
in = input('Enter code: ', 's');
[~,~,raw] = xlsread('examplesheet.xlsx');
p = strcmp(in,raw);% Compare user input string with entries in the Excel sheet
rowNum = find(p==1)%Get Row number
this works for my purpose as well, with 1 caveat: it can only search the first sheet in an excel sheet. is there any way to get this to look through multiple sheets, and return the row and sheet name in 2 different variabvles? I have included an example sheet here.

Accepted Answer

Walter Roberson
Walter Roberson on 28 Jan 2020
filename = 'examplesheet.xlsx';
[~, sheets] = xlsfinfo(filename);
rows_found = [];
sheets_found = {};
for K = 1 : length(sheets)
this_sheet = sheets{K};
[~, ~, raw] = xlsread(filename, this_sheet);
[rowNum, colNum] = find( strcmp(in, raw));
if ~isempty(rowNum)
rows_found = [rows_found; rowNum];
sheets_found = [sheets_found; repmat({this_sheet}, length(rowNum), 1)];
end
end
Output is a numeric matrix rows_found and a cell array of character strings sheets_found . rows_found(K) is arow number and sheets_found{K} is the corresponding sheet name.
  27 Comments

Sign in to comment.

More Answers (0)

Categories

Find more on Application Deployment in Help Center and File Exchange

Tags

Products


Release

R2019b

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!