Search comma separated string formula

[mattyro1]

Good day all,

I am hoping someone can assist with my issue below.

I have a column with various comma separated values, I would like to search for a particular answer within the string.

Example: Column R contains the following: WD,WL, LI,AI,LD

Column S: I would like to search for anything within the string that contains "*W*" and "*A*"...yes this would be a wild card search.

Also if any way possible to join the answers back into a comma separated value would be great.

Any assistance is always greatly appreciated.

 

[jbeaucaire]

I would imagine a custom User Defined Function would have to be used. Is VBA ok?

 

[Rick Rothstein]

I would like to search for anything within the string that contains "*W*" and "*A*"...yes this would be a wild card search.

How complex do the wildcard searches have to be? Will they be single letter searches only (as you have shown) or could they be more involved?

Also if any way possible to join the answers back into a comma separated value would be great.

Does the existing order need to be preserved when the codes are joined back into the comma separated value?

 

[mattyro1]

I would imagine a custom User Defined Function would have to be used. Is VBA ok?

Thank you for your reply and my apologies on just getting back to this issue. Yes VBA is okay.

 

[mattyro1]

How complex do the wildcard searches have to be? Will they be single letter searches only (as you have shown) or could they be more involved?


Does the existing order need to be preserved when the codes are joined back into the comma separated value?

Thank you for your reply Rick.

The wild cards are very simplistic and I do not see they any more complicated than single letter.

The existing order does not necessarily need to be preserved when the codes are joined back into the comma separated value.

Again thank you for taking the time out to assist.

 

[Rick Rothstein]

The following is a UDF (user defined function) that might do what you want.

Function GetValues(ColumnRangeToSearch As Range, ParamArray TextToFind() As Variant) As String
  Dim X As Long, Values As Variant
  Values = WorksheetFunction.Transpose(ColumnRangeToSearch)
  For X = LBound(TextToFind) To UBound(TextToFind)
    GetValues = GetValues & Join(Filter(Values, TextToFind(X), True, vbTextCompare), ", ") & ","
  Next
  GetValues = Left(GetValues, Len(GetValues) - 1)
End Function

As written, the UDF does not use a second column at all, mainly because I wasn't sure how you wanted to make use of it, but that can be changed if you absolutely need to make use of a second column. For the example you gave us, you would obtain your desired list by using this formula...

=GetValues(R1:R5,"W","A")

Note that no wildcards are required... just list the letter or letters (you are not restricted to single letter searches) in a comma separated list after the column range to search. You could also call this function from within your own VB code if you needed to,for example, like this

MsgBox GetValues(Range("R1:R5"), "W", "A")

NOTE: This function will duplicate a value as many times as it satisfies the individual letter filters. For example, for your data, this formula...

=GetValues(R1:R5,"W","L")

would produce this list...

WD, WL,WL, LI, LD

where you can see WL has been repeated because it contains both the "W" and the "L" that was searched for.

Does this UDF meet your needs or do you need it modified in some way?

 

[mattyro1]

The following is a UDF (user defined function) that might do what you want.

Function GetValues(ColumnRangeToSearch As Range, ParamArray TextToFind() As Variant) As String
  Dim X As Long, Values As Variant
  Values = WorksheetFunction.Transpose(ColumnRangeToSearch)
  For X = LBound(TextToFind) To UBound(TextToFind)
    GetValues = GetValues & Join(Filter(Values, TextToFind(X), True, vbTextCompare), ", ") & ","
  Next
  GetValues = Left(GetValues, Len(GetValues) - 1)
End Function

As written, the UDF does not use a second column at all, mainly because I wasn't sure how you wanted to make use of it, but that can be changed if you absolutely need to make use of a second column. For the example you gave us, you would obtain your desired list by using this formula...

=GetValues(R1:R5,"W","A")

Note that no wildcards are required... just list the letter or letters (you are not restricted to single letter searches) in a comma separated list after the column range to search. You could also call this function from within your own VB code if you needed to,for example, like this

MsgBox GetValues(Range("R1:R5"), "W", "A")

NOTE: This function will duplicate a value as many times as it satisfies the individual letter filters. For example, for your data, this formula...

=GetValues(R1:R5,"W","L")

would produce this list...

WD, WL,WL, LI, LD

where you can see WL has been repeated because it contains both the "W" and the "L" that was searched for.

Does this UDF meet your needs or do you need it modified in some way?

Thank you Rick as I appreciate your willingness to assist.

This is kind of close to what I am looking for with one exception.

The entire list is actually in one cell and that is actually what I will need to extract.

So your example gave me the following:

Formula: =GetValues(R63:R64,"W","L")

Column: R63 = AI, LI
Column: R64 = Li, AI

Results: AI,LI,LI,AI

What I am looking for is: In Cell R63, in the cell contains "WD,LI,AI,LD"

If the Formula were =Getvalues(R63:R63, "W","L") I would like the return to be: WD,LI,LD

Hopefully this helps further explain what I am trying to accomplish.

Thank you again for your assistance.

 

[Rick Rothstein]

Ah, all the text is in one cell. Okay, I think this will do what you want then...

Function GetValues(ByVal S As String, ParamArray TextToFind() As Variant) As String
  Dim X As Long, Unique As String, V As Variant, Parts() As String, Coll As New Collection
  Const Delimiter As String = ", "
  Do While InStr(S, ", ")
    S = Replace(S, ", ", ",")
  Loop
  Parts = Split(S, ",")
  For X = LBound(TextToFind) To UBound(TextToFind)
    For Each V In Filter(Parts, TextToFind(X), True, vbTextCompare)
      Coll.Add V, V
    Next
  Next
  For Each V In Coll
    GetValues = GetValues & Delimiter & V
  Next
  GetValues = Mid(GetValues, 3)
End Function

The first argument is now a String value rather than a pure Range... this means you can pass in a String variable from your own VB code...

MsgBox GetValues(CommaDelimitedText, "W", "L")

or a single cell reference from the worksheet...

=GetValues(R63,"W","L")

 

[mattyro1]

By Goly I think we got it!

Thank you Rick, you have surely made my Monday much easier to cope with.