How to find the location of the first string matching my searching criteria?

My goal is to find the column number of the variables stored in my Excel file. Below is my code. Now B stores the column headers. For example B{1,1} is the first variable name, B{1,2} is the second header name, and so on.

% --- load the file ---
File1 = ['workingFolder/abc.xlsx'];
[A, B C] = xlsread (File1); % B stores the column headers of my Excel file

How do I write a program so that B{N,2} (N is the number of the column I'm hoping to get) would meet the below searching criteria :

a) It has to contain both string1 and string2.

b) N is the first column meeting that criteria.

For example, a column header with the name of 'ABCDEFG_GKLM' would meet the search criteria of having both 'BCD', and 'KL' in the term.

Many thanks!

[Image Analyst]

Please attach an actual Excel workbook with the paper clip icon, and say what you want in the example, like what Excel cell and what string(s).

[Leon]

Here is an example Excel file. I want to program automatically identify which column is Year, which one is Longitude, which one is oxygen flag, 'CTDOXY_flag', etc. The headers are not standardized. So sometimes it could be CTDOXY_flag, sometimes it could be CTDOxygen_flag_w, etc.

Thank you.

Answer 1

[Jon]

Assuming you can get a cell array B of character vectors containing the headers, and the strings you want to match assigned to string1 and string2 then this should work:

% get vector of logicals whose elements are set to true wherever both matching criteria are met
% use 'IgnoreCase',true to make it insensitive to the case of the headers
isMatch = contains(B,string1,'IgnoreCase',true) & contains(B,string2,'IgnoreCase',true)

% now find the index of the first match, this will be the column number you are looking for
N = find(isMatch,1,'first')

[Leon]

Many thanks for the code! This is amazing!

One caveat. As you can see from my example.xlsx. There are two rows of headers. That have cause the program to give out wrong info. How do I limit the search to row #1 of B only?

I tried to use B{1, :} and that it did not work. For cells, how do I specify an entire row like I do for data values, e.g., data(1,:);

Thanks.

[Jon]

Hello Leon,

I think if you use B(1,:) , that is use round brackets not curly brackets, you will get what you want. The difference between using round bracket and curly brackets for cell arrays is always a little tricky.

[Leon]

Many thanks, Jonathan! Really appreciate your big help.

[Stephen23]

"The difference between using round bracket and curly brackets for cell arrays is always a little tricky."

The difference is simple:

• () parentheses refer to the cells themselves, whereas
• {} curly braces refer to the cell contents.

This is consistent for all MATLAB array types: using parentheses always returns a part of the array itself, so if you use parentheses on a cell array you will get a cell array.

https://www.mathworks.com/help/matlab/matlab_prog/access-data-in-a-cell-array.html

[Leon]

Thank you Stephen for the explanation!