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]

Is all of that text in a single cell? If not, please explain your layout.

 

[BrianBank]

Yes all of the text is in one cell. The formula needs to search through the columns H3 - R3 though and look at each cell for the keyword and then return everything in between the commas where it finds that keyword.

 

[Rick Rothstein]

Yes all of the text is in one cell. The formula needs to search through the columns H3 - R3 though and look at each cell for the keyword and then return everything in between the commas where it finds that keyword.

I am not sure how to do that with a built-in formula, but you can use a UDF (user defined function). Here is one that will work...

[table="width: 500"]
[tr]
	[td]Function GetText(FromCell As Range, FindMe As String) As String
  GetText = Join(Filter(Split(Replace(FromCell, ", ", ","), ","), FindMe, True, vbTextCompare), ", ")
End Function[/td]
[/tr]
[/table]

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use GetText just like it was a built-in Excel function. For example, assuming cell H3 contains the text you want to search for the word "Poland" in...

=GetText(H3,"Poland")

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[BrianBank]

Rick,

Your help is incredible and I want to thank you. One last issue - how do I do the search so it's searching in all of the columns and not just H3? I need it to look in each cell from Column I2 - S2

 

[Rick Rothstein]

Rick,

Your help is incredible and I want to thank you. One last issue - how do I do the search so it's searching in all of the columns and not just H3? I need it to look in each cell from Column I2 - S2

Once created, a UDF is no different than a built in Excel function so you would use it the same way... put it in a formula in a cell where you want it to display its results and then copy that cell either across or down depending on the range of cells you want it to cover. So, for what I understand of your layout, put this in an unused row in Column I...

=GetText(I2,"Poland")

and copy it across to cell S2.

 

[BrianBank]

And to add to my latest post, if the cell is blank it wouldn't return anything as it searches through the various cells from I2 - S2. Is there any way to make that work for multiple cells in a row and not just H3?

 

[BrianBank]

Sorry let me clarify as you are extremely helpful.

I would like all of the code to return in one cell if it finds the data in the columns I2 - S2.

I have the code in a column H2 that looks through each cell from I2 - S2 and returns them in that one cell. So if Poland was found in I2 and M2, it would just be inputted into the H2 cell where the code is.

=GetText(I3:S3,T3)

It seems to be giving me an error?

Thanks again for all your help

 

[Rick Rothstein]

Sorry let me clarify as you are extremely helpful.

I would like all of the code to return in one cell if it finds the data in the columns I2 - S2.

I have the code in a column H2 that looks through each cell from I2 - S2 and returns them in that one cell. So if Poland was found in I2 and M2, it would just be inputted into the H2 cell where the code is.

=GetText(I3:S3,T3)

Replace the function I gave you earlier with this one and try your formula then (making sure you specify the correct row number)...

[table="width: 500"]
[tr]
	[td]Function GetText(HorzRng As Range, FindMe As String) As String
  Dim Cell As Range
  For Each Cell In HorzRng
    If InStr(1, Cell.Value, FindMe, vbTextCompare) Then GetText = GetText & ", " & Join(Filter(Split(Replace(Cell.Value, ", ", ","), ","), FindMe, True, vbTextCompare), ", ")
  Next
  GetText = Mid(GetText, 3)
End Function[/td]
[/tr]
[/table]

 

[BrianBank]

Thanks Rick this worked perfectly!!!

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