Extract word from a delimited string

I've received a data set which contains a delimited string where I need to extract a particular word from that data.

I got the result but as a learner looking for the better solution than this formula.

=LET(a,$A$2:$A$12,b,IFS(SEARCH("Similar",a),"Similar"),c,IFS(SEARCH("OTT",a),"OTT"),d,IFS(SEARCH("QA Time",a),"QA Time"),e,IFS(SEARCH("Hard Limit",a),"Hard Limit"),IFERROR(b,IFERROR(c,IFERROR(d,IFERROR(e,a)))))

Thank you.

If you do not have to worry about one Actual Result being contained within another Actual Result, then you can use a lookup table. In the formula below,

• the Lookup Table is hard-coded
  • You could create a separate lookup table and reference that within the formula
• I used a Table structure with structured references which enables the formula to fill-down the column automagically and adjust for insertions or deletions
  • You could change it to regular addressing if you prefer.
• ***Note that if there is more than one keyword in an "Audit Type", only the first keyword (by order in the list of keywords) will be returned. This is the case for your second Audit Type.

=LET(
    a, {"Similar"; "QA Time"; "OTT"; "Hard Limit"},
    INDEX(a, XMATCH(TRUE, ISNUMBER(SEARCH(a, [@[Audit Type]]))))
)

If it is the case that there are multiple Keyword in a single row, as in row two, and you wish to return all of them, you can use the following formula: *(Note that here I used a separate table for the lookups, named Keywords)

=ARRAYTOTEXT(
    CHOOSEROWS(
        Keywords,
        LET(
            a, BYROW(
                Keywords,
                LAMBDA(arr, ISNUMBER(SEARCH(arr, [@[Audit Type]])))
            ) * SEQUENCE(ROWS(Keywords)),
            b, FILTER(a, a > 0),
            b
        )
    )
)

If there might be a problem with one keyword being included within another, then you should probably be looking for a solution using regular expressions.

At present, only the Insider edition has regular expressions but you could use VBA or Python to implement it in Excel.