Search text and use specific formula for text found

Question

I have a table like this

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 :

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

Post your input and then show your desired output. To run formula from cells, you need `VBA` coding. — Harun24hr, Aug 10 '23 at 08:38
@Harun24hr I've edited my post. I won't use VBA. I'll use a workaround for my formulas. This is another debate. Here I'm trying to understand how I can search for texts in a cell and get reference of the text I've found in my table (1st table) — EagleWatch, Aug 10 '23 at 08:52
Do you have any chance to match multiple value from list of `A2:A6` cells in single cell of your input (in screenshot)? — Harun24hr, Aug 10 '23 at 09:01
What version of Excel have you got? Also, can you post the data as markdown sample data instead? — JvdV, Aug 10 '23 at 09:10
Input data is in plain text now. @JvdV I'm using the last version from O365 product. — EagleWatch, Aug 10 '23 at 09:20

JvdV · Answer 1 · 2023-08-10T10:41:10.250

5

Here is one option:

Formula in B2:

=MAP(A2:A4,LAMBDA(s,LET(x,TEXTSPLIT(s," "),@SORT(XLOOKUP(D2:D6&"*",x,x,"",2),,-1))))

Note: This assumes that your listvalues are always at the start of a word (string get's split on space). If not the case, then still split it but use an extra asterisk in your lookup:

=MAP(A2:A4,LAMBDA(s,LET(x,TEXTSPLIT(s," "),@SORT(XLOOKUP("*"&D2:D6&"*",x,x,"",2),,-1))))

edited Aug 10 '23 at 10:41

answered Aug 10 '23 at 09:22

JvdV

70,606
8
39
70

1

Shorter one. I forgot about `xlookup()` wild card matching option. – Harun24hr Aug 10 '23 at 09:24
Thank you. It's almost working but in some cases, the word I have to find can be join on other word (like zetrthRC-LFO-FIN-123456-zertsn) – EagleWatch Aug 10 '23 at 09:51
Then simply add an asterisk in front of the 1st parameter in xlookup – JvdV Aug 10 '23 at 10:20

score 3 · Accepted Answer · answered Aug 10 '23 at 09:21

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.

Search text and use specific formula for text found

2 Answers2