Forum Discussion
Find text within a comma delimitated string
=OR(TEXTSPLIT(A1,",")="abc")
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”.
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.
- 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.
