Search for specific Words within a sentence and return the specific word if found

Hey everybody, I dont know how to exactly describe it, but I will try:

I do have a long list of titles of SAP-entries which are following a specific nomenclature. They are all named like: "prefix_specific word". I do have a total amount of 11 words which can stand directly after the prefix, replacing "specific word" within "prefix_specific word". I would like, that Excel is analyzing my SAP-entries in regard to the specific word an should return/extract this specific word to a new cell.

As an example I have a title of: "prefix_specific word_further words explaining more". The formula should return in the new cell only "specific word" if this word is found within the cell. It should be something like an automatic search/filter function.

I thought I can build something like a =XLOOKUP, but I was not able to do it.

Do you have any idea?

Thank you very much,
Chris

Originally Posted by TMS

Maybe this:

Formula:

Please Login or Register  to view this content.

And, if your formula does work for the OP, but if he (or a reader of this thread) does not have the TEXTSPLIT function yet, then this formula will also work....

Formula:

Please Login or Register  to view this content.

Hey everybody,

thanks for the quick reply. I think that trimming will not work due to potential differences in the length of the first prefix.
I attached an example to make clear what I want to do.
I would like to define search strings like in line E. There I wrote down 5 names. Excel should search for this names in line B and should write down the result in line C if Excel found a name which is defined in E to the line C.
If no name could be found, Excel should display "others".

Thank you very much and have a nice day,
Chris

Example.xlsx

Hey John,

thank you for your reply.
Looks good, but now imagine, that I will have around 300 entries in line B and a search string of around 5 names in line E. I think that the formula is not working anymore if the order is mixed-up?
For example, if I add further lines with ABC_Malfoy_Expecto the formula answers with "Others" instead of Malfoy again.

Thank you very much,
Chris

Might be over-complicating it but how about:

=LET(x,BYROW(IF(ISNUMBER(SEARCH(TRANSPOSE($E$2:$E$6),$B$2:$B$6)),TRANSPOSE($E$2:$E$6),""),LAMBDA(row,TEXTJOIN("_",1,row))),IF(x="","Others",x))

If it finds more than one term then it will return them all separated by an underscore.