Excel: Searching for text within a cell array and returning text from that cell-CodePudding

In A7, I would like to return the number at the beginning of the cell in which I find a certain piece of text. So, for example, if I search for the word "question" I would like to return the number 4.

I have tried using this:

=LEFT(CELL("address",INDEX(A1:A5,MATCH(""&"question"&"",A1:A5,0))),1)

Because CELL("address",INDEX(A1:A5,MATCH(""&"question"&"",A1:A5,0))) returns the cell reference ($A$4), but it takes the cell reference as the text rather than the text in that cell.

Can anyone help?

CodePudding user response：

This formula is not perfect because it cannot handle if there is more than one match or if the number to extract is over 9 but could be a starting point:

Formula in B9:

=LEFT(INDEX($A$1:$A$5,MATCH("*"&B8&"*",$A$1:$A$5,0)),1)

CodePudding user response：

For Microsoft-365 user can utilize FILTER() function.

=LEFT(FILTER(A1:A5,ISNUMBER(SEARCH(C2,A1:A5))),1)