Extract or return all value/text that matching to multiple criterias/condition

New Contributor

Screenshot 2023-01-01 143821.jpg


In the 1st column, there are a list of countries separated by comma. In the 2nd column, i would like to get a result where it will show or extract the country that matching either to following criteria/condition= China or Hong Kong or Taiwan.


Please advise. Thank you.




5 Replies
best response confirmed by HZ25062016 (New Contributor)


Thanks for the sample. Excel version + OS still missing. Below I assumed Excel 2021 or 365 is in use:




EDIT: On reflection, in C3:

=TEXTJOIN(", ",,IF(COUNTIF(B3, "*" & {"China","Hong Kong","Taiwan"} & "*"),{"China","Hong Kong","Taiwan"},""))



wow! this work well, thank you very much, really help
You're welcome & Thanks for providing feedback