excelexcel-formula

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.

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.

Pic


Solution

  • 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,

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

    enter image description here

    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
            )
        )
    )
    

    enter image description here

    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.