Search code examples
excelexcel-formulafindoffice365

Search text and use specific formula for text found

I have a table like this

Text to find

On the left : list of text I'm looking for in a cell. On the right : formula I'll use depending on the text I've found. The formula is necessary for a text extraction (which is not the same regarding the text I've found).

The question is : How can I search for this list of text in one cell and then returning the associated formula to use ?

EDIT : What I'm looking for : enter image description here

As you can see, I have random texts in each lines, and I want to extract specific content. I cannot use If..if..if.. because of the Excel limitation.

Inputs :

lrh34gero egepjpj I28595474 erqm567goh

gerlkq $ONE-234556 ethrh3444rzh

zrlthk 4555 njwhv ùpbozj LFO-FIN-25436545

Solution

  • Use nested BYROW() function with FILTERXML(). Try-

    =BYROW(B3:B4,LAMBDA(z,TEXTJOIN("",1,BYROW(A2:A4,LAMBDA(x,IFERROR(FILTERXML("<t><s>"&SUBSTITUTE(z," ","</s><s>")&"</s></t>","//s[starts-with(., '" &x&"')]"),""))))))

    To know details about FILTERXML() please read this article from JvdV.

    enter image description here