How do I return text when it finds a keyword?

[BrianBank]

Hello All -

You were extremely helpful on my post about looking through multiple columns to see if a keyword was in there, but was wondering if you could help on this. The below is the formula you provided on my original post accomplished finding if a certain keyword was in columns H3 - R3.

=IF(COUNTIF(H3:R3, "*" & U3 & "*")>0, U3, "")


How can I use the above formula and look for the word "Poland" , but return "EMEA - Poland - BHW - GF - Finance" and EMEA - Poland - GF - Securities below. I'm essentially needing something where the formula knows to bring in all of the text between the commas where it finds its hit. As you can see the keyword could show up multiple times. As you can imagine the keyword can change, so I just need to see if it can find the keyword in a cell and bring in all of the text between the commas.


EMEA - GF - Finance - Product Control, ASPAC - Malaysia - GF - Finance, ASPAC - Taiwan - GF - Finance, ASPAC - GF - Finance - Product Control , EMEA - Romania - GF - Finance, LATAM - El Salvador - GF - Finance, EMEA - Tunisia - GF - Finance, ASPAC - Japan - GF - Finance, ASPAC - Vietnam - GF - Finance, ASPAC - South Korea - CKI - GF - Finance, EMEA - Russia - GF - Finance, LATAM - GF - Finance - Product Control , LATAM - Argentina - GF - Finance, ASPAC - Australia - GF - Finance, LATAM - Brazil - GF - Finance, EMEA - Kenya - GF - Finance, ASPAC - Singapore - GF - Finance, ASPAC - Hong Kong - GF - Finance, EMEA - Pakistan - GF - Finance, ASPAC - Philippines - GF - Finance, ASPAC - India - GF - Finance, EMEA - Poland - BHW - GF - Finance, EMEA - Turkey - GF - Finance, LATAM - Colombia - GF - Finance, ASPAC - Indonesia - GF - Finance, EMEA - South Africa - GF - Finance, EMEA - Israel - GF - Finance, ASPAC - China - GF - Finance, EMEA - Uganda - GF - Finance, EMEA - Czech - GF - Finance, ASPAC - Thailand - GF - Finance, EMEA - Hungary - GF - Finance, EMEA- Poland - GF - Securities, EMEA - Kazakhstan - GF - Finance, LATAM - Mexico - GF - Finance - Product Control , EMEA - UAE - GF - Finance, ASPAC - Indonesia - PT CSI - GF - Finance, LATAM - Panama - GF - Finance, LATAM - Guatemala - GF - Finance, EMEA - Qatar - GF - Finance, LATAM - Venezuela - GF - Finance, LATAM - Ecuador - GF - Finance, LATAM - Chile - GF - Finance, ASPAC - South Korea - CGMK - GF - Finance, EMEA - Slovakia - GF - Finance, LATAM - Costa Rica - GF - Finance

 

[Rick Rothstein]

Thanks Rick this worked perfectly!!!

You are quite welcome... I am glad I was able to help.

Honestly, I don't know how you're so good at this.

I am not so sure about the "how good I am at this" part, but after having been writing code nearly every day for some 37 years now, I find I can slap together workable code for a fair amount of situations without spending too much time doing so.