Forum Discussion
Find text within a comma delimitated string
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?
=OR(TEXTSPLIT(A1,",")="abc")
10 Replies
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 FunctionMy 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”.
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 FunctionSteps to Implement:
- Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
- Go to Insert > Module to insert a new module.
- Copy and paste the above VBA function into the module.
- Close the VBA editor.
- 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.
- 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 charactersIs this your expected outcome?
abc TRUE abc,xyz TRUE idf,abc TRUE pdq,abc,xyz TRUE mabc,xyz TRUE idf,abcd TRUE Xabc,idk FALSE Idk,xabc FALSE
