CODEWITHSUNDEEP
excelexcel-formula
Chandrakant
Chandrakant

Reputation: 21

Searching for multiple text strings in a MS Excel cell and return the same string when found

For example, in Excel cells, I have these texts

A1 "Hi_how_are_you_today_?"

A2 "I_am_doing_great"

A3 "everything good at you workplace?"

A4 "Doesn't have any of these words"

I am looking for 3 words How, Great and workplace. In any cell if any of these words is found then it should return the same word, else it should a return blank value.

I was able to write this formula on Excel but it is returning #N/A error if any of these values aren't found. =IFS(ISNUMBER(SEARCH("How",A1))=TRUE,"How",ISNUMBER(SEARCH("Workplace",A1))=TRUE,"Workplace",ISNUMBER(SEARCH("great",A1))=TRUE,"great")

Can we make some changes in this formula so it will return blank if any of these is not found?

Upvotes: 1

Views: 15512

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34370

You can also try

=IFERROR(INDEX({"how","great","workplace"},MATCH(TRUE,ISNUMBER(SEARCH({"how","great","workplace"},A1)),0)),"")

Upvotes: 2

Jerry
Jerry

Reputation: 71598

Simply add a condition that will always be true at the end:

=IFS(ISNUMBER(SEARCH("How",A1)),"How",ISNUMBER(SEARCH("Workplace",A1)),"Workplace",ISNUMBER(SEARCH("great",A1)),"great", TRUE, "")
                                                                                                                         ^^^^^^^^

Also, I dropped the =TRUE in the formula, since they are unneeded.

Upvotes: 1

Related Questions