CODEWITHSUNDEEP
exceltextexcel-formulatextjoin
DirtAndPlantScientist
DirtAndPlantScientist

Reputation: 43

Search a single cell for multiple words AND return all words found

I have been trying all day to write a formula in Excel that can search a string for multiple keywords and then print which keywords it found in a cell.

I've gotten as far as searching for a list of keywords and returning the first keyword found, but I have no earthly idea how to get it to return subsequent matches. Example, complete with formula (pasted below as well):

Apparently I'm not allowed to embed images so this is a link.

=INDEX($C$2:$C$8,MATCH(1,COUNTIF(B2,"*" & $C$2:$C$8 & "*"),0))

All I want is for that A2 cell to contain "cat; mongoose" instead of just "cat". All the searches I do are coming up with solutions involving returning multiple cells or searching multiple cells. I don't even know if this is something I can do with this function, because I didn't know this function existed until three hours ago. I hacked this one together from the directions on this webpage.

And that was great! But now I'm stuck and confused and frustrated. I can't tell if I need to find a UDF, or if there's some pre-existing VBA someone, somewhere has written for this, so any help is appreciated!

Upvotes: 3

Views: 3675

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

INDEX will only return one value. Use TEXTJOIN as an array formula

=TEXTJOIN("; ",TRUE,IF(ISNUMBER(SEARCH(C$2:$C$8,B2)),C$2:$C$8,""))

Being an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

TEXTJOIN was introduced with Office 365 Excel. If you do not have Office 365, you will need separate cells for each word returned or vba.

Upvotes: 2

Related Questions