Finding the cell index of a string using a substring in MS Excel

Question

I am trying to complete a task in an Excel spreadsheet. I have a list of strings, each of which is a substring of some slightly longer string. What I would like to do is to search each substring (a cell index) against a range of longer strings (a range of cells in a column) and return the actual cell index plus 1 (or really the numerical part of that cell index plus 1, e.g. B451 => 451+1 = 452).

So for example, I would like to search string "AAA" in cell G2 of sheet sorted_data against the cell range B2:B1001 in sheet data_all and would like the cell index + 1 of range B2:B1001 in which string "BAAAB" is found, e.g. 452 (if "BAAAB" is in cell B451). The strings and substrings are unique so there is no chance of multiple hits.

The formula has to be extended down 1000 columns of sheet sorted_data but the search range remains the same throughout.

I have the formula

=COUNTIF('data_all'!$B$2:$B$1001,"*"&'sorted_data'!G2&"*") > 0

This only return a TRUE or FALSE though, not what I want. Any ideas?

@TerryW the sequences are biological sequences, e.g. "FQILGALST" being the substring and "CNYPIHAANRELSIFQILGALSTLL" being the actual string. They are all unique as I validated that when generating these random sequences in a Python script. There isn't really a bigger picture at the moment, just a menial task I have to do in order to make data SPSS readable in a manner such that performing correlation analyses is easy. — Spyros
– Spyros, Commented Sep 13, 2019 at 0:07

Scott Craner · Accepted Answer · 2019-09-13 00:18:25Z

2

Use MATCH to return the row number + 1

=MATCH("*"&G2&"*",$B:$B,0)+1

The MATCH("*"&G2&"*",$B:$B,0) returns the row number and the +1 adds one row.

You can then use INDEX to return the value in any column desired in that row:

=INDEX($A:$A,MATCH("*"&G2&"*",$B:$B,0)+1)

This would return the value in column A in the row beneath where the match was found.

As you can see we use full column references so the data can grow without detriment. And INDEX/MATCH is supremely optimized so the number of formulas will not make that big of impact on the calc times.

edited Sep 13, 2019 at 0:18

answered Sep 13, 2019 at 0:11

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Spyros Over a year ago

Thank you so much for your explanation and your response!

rasputin Over a year ago

You can also use XMATCH("*"&G2&"*", $B:$B, 2) if you prefer

Collectives™ on Stack Overflow

Finding the cell index of a string using a substring in MS Excel

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related