CODEWITHSUNDEEP
excelexcel-formula
Terrace Grow
Terrace Grow

Reputation: 21

Finding certain words in single cell

I'm trying to find specific word out of a list of words in a single cell and output a value if true but I think i'm taking the wrong approach for this issue.

Basically I get a description text in a cell and if in that cell exists one of these specific words it should return a value if true.

Numbers , reporting ,jobs ,administration

=IF(ISNUMBER(SEARCH("numb3r5";X16));"numb3r5";
    IF(ISNUMBER(SEARCH("Administration";X16));"AdminNumbers";
       IF(ISNUMBER(SEARCH("Reporting";X16));"Reporting";
          IF(ISNUMBER(SEARCH("Job";X16));"Jobs";
             IF(ISNUMBER(SEARCH("numbers";X16));"numb3r5";
                NA())))))

I got this very complicated nested IF formula which kind of does the trick but for example in the following text the word "Reporting" is above "Numbers" it will be ignored because it will first search for "Numbers" because this is the first IF statement and it returns TRUE and exits the formula and never reaches the Reporting If statement.

Sample text:

Solution KR00040079: Technische Störung: Reporting AW meldet einen Fehlermeldung beim Anmelden von Numbers. (siehe Attachment) AW habe diese Fehlermeldung seit ca heute um 9.

It returns "Numbers" here because it is the first statement and its through after scanning the whole text in that cell.

Is there any otherway this could be done ? A formula that would search for multiple values within order of how the text is formatted ?

Thanks

Upvotes: 1

Views: 131

Answers (1)

JvdV
JvdV

Reputation: 75960

You could try:

enter image description here

Formula in B1:

=LET(X,{"Numb3r5","Reporting","Jobs","administration"},Y,{"Numbers","Reporting","Jobs","administration"},Z,SORT(CHOOSE({1,2},X,SEARCH(Y,A1)),2),IF(ISNUMBER(INDEX(Z,1,2)),INDEX(Z,1,1),"Not Available"))

Upvotes: 2

Related Questions