SOLVED

Find text within a comma delimitated string

Copper Contributor

I have a column with comma delimitated data string:

1) abc

2) abc,xyz

3) idf,abc

4) pdq,abc,xyz

5) mabc,xyz

6) idf,abcd

 

I am trying to show any data set with ID abc in it and i can pick out with a formula the solo abc and ,abc, but I’m having issues with the sets where abc is included within another ID as shown in set 5) & 6). Any suggestions?

10 Replies

@Mistermonopoly 

=ISNUMBER(SEARCH("abc",A1))

 

@Mistermonopoly 

To identify rows in a column that contain "abc" as a standalone item in a comma-delimited string, you can use a combination of functions to ensure that you correctly identify cases where "abc" is an exact match, and not just part of another substring.

=IF(ISNUMBER(SEARCH(",abc,", "," & A1 & ",")), "Yes", "No")

You can use to check if the string "abc" appears as a standalone item in a comma-delimited list.

Attached is an example file.

 

Additional a VBA code (code is untested, please backup your file first):

 

Function ContainsStandaloneABC(inputString As String) As Boolean
    Dim modifiedString As String
    Dim searchString As String
    
    ' Add commas to the beginning and end of the input string
    modifiedString = "," & inputString & ","
    
    ' Define the search string
    searchString = ",abc,"
    
    ' Use InStr to check if the search string exists in the modified string
    If InStr(modifiedString, searchString) > 0 Then
        ContainsStandaloneABC = True
    Else
        ContainsStandaloneABC = False
    End If
End Function

 

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

 

Was the answer useful? Mark as best response and like it!

This will help all forum participants.

@NikolinoDE that works for find the ID between the commas which I was doing with a find “,abc,” function already. I am struggling more with instances 5 & 6 where they fall either in front “xabc” or at the end “abcx”. 

This is capturing any of the abc instances within the string even false ones.
Xabc,idk =true should be false
Idk,xabc = true should be false

I’m looking for a formula to filter these out and retain the abc w/o Any pre/postceeding characters

@Mistermonopoly 

Try with a Custom Function in VBA, you can write a custom function to check if "abc" appears as a standalone item in a comma-delimited string, regardless of its position within the string. Below is a VBA function that you can use:

Vba Code (find in insert file):

Function FindABC(ByVal inputString As String) As String
    Dim dataArray() As String
    Dim element As Variant
    
    ' Split the input string into an array using comma as delimiter
    dataArray = Split(inputString, ",")
    
    ' Loop through each element in the array
    For Each element In dataArray
        ' Check if "abc" is found in the element
        If InStr(1, Trim(element), "abc", vbTextCompare) > 0 Then
            ' If "abc" is found, return "Yes" and exit the function
            FindABC = "Yes"
            Exit Function
        End If
    Next element
    
    ' If "abc" is not found in any element, return "No"
    FindABC = "No"
End Function

Steps to Implement:

  1. Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
  2. Go to Insert > Module to insert a new module.
  3. Copy and paste the above VBA function into the module.
  4. Close the VBA editor.
  5. Now, you can use this custom function FindABC in your Excel sheet.

In Excel, in column C, enter the formula =FindABC(A2) where A2 is the cell containing your data. Then, drag the formula down for all rows to check each cell in column A.

This VBA function will correctly identify instances where "abc" appears.

@Mistermonopoly 

Is this your expected outcome?

abcTRUE
abc,xyzTRUE
idf,abcTRUE
pdq,abc,xyzTRUE
mabc,xyzTRUE
idf,abcdTRUE
Xabc,idkFALSE
Idk,xabcFALSE

abc TRUE
abc,xyz TRUE
idf,abc TRUE
pdq,abc,xyz TRUE
mabc,xyz ***FALSE***
idf,abcd ***FALSE***
Xabc,idk FALSE
Idk,xabc FALSE

Corrected to desired results
Thank you I may end up trying this through VBA as a last resort. I was really trying to stick to formulas so the rest of my team could work with the sheet on the fly.
best response confirmed by Mistermonopoly (Copper Contributor)
Solution

@Mistermonopoly 

=OR(TEXTSPLIT(A1,",")="abc")
That covered all of the bases thanks a lot
1 best response

Accepted Solutions
best response confirmed by Mistermonopoly (Copper Contributor)
Solution

@Mistermonopoly 

=OR(TEXTSPLIT(A1,",")="abc")

View solution in original post