I've received a data set which contains a delimited string where I need to extract a particular word from that data.
Audit Type | Actual Result |
---|---|
Similar (BAU) Process | Similar |
Hard Limit - QA Time Process | QA Time |
1 - Similar (BAU) Audit | Similar |
5 - QA Time | QA Time |
3 - OTT/LP Audit | OTT |
2 - Hard Limit / Training | Hard Limit |
4 - EXTND OTT/LP Audit | OTT |
Hard Limit | Hard Limit |
OTT/LP Audit | OTT |
OTT Audit | OTT |
EXTND OTT Audit | OTT |
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,
Lookup Table
is hard-coded
Table
structure with structured references which enables the formula to fill-down the column automagically and adjust for insertions or deletions
=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.