Can you be a bit more specific with a few examples in a worksheet?
Hey all;
I am trying to search a string of characters in a cell and determine if any of several different combinations of characters I define exist in the string. The character string can vary from 1 to 10 characters and the characters appear in random sequence and location.
I have been using;
IF(ISNUMBER(SEARCH($B$24,F15)),"Yes",IF(ISNUMBER(SEARCH($B$25,F15)),"Yes",IF(ISNUMBER(SEARCH($B$26,F15)),"Yes","No")))
and it only works if the characters I define exist in sequence and next to each other in the string.
I need the formula to identify the presence of the 2 characters regardless of their position or sequence in the string.
Suggestions?
Keih
Can you be a bit more specific with a few examples in a worksheet?
_________________________________
Best Regards
Nicolas Michael
Test this UDF:
Open your workbook, then save it as "Excel Macro Enabled Workbook".Code:Public Function Testcombinations(InputString As String, SearchRng As Range) As String Dim IncompatibilityRng As Range Dim CombinationToSearch As String Dim CharLength As Long, OutCounter As Long, InCounter As Long CharLength = Len(InputString) OutCounter = 1 InCounter = 1 Do While OutCounter <= CharLength Do While InCounter <= CharLength If OutCounter <> InCounter Then CombinationToSearch = Mid$(InputString, OutCounter, 1) & Mid$(InputString, InCounter, 1) Set IncompatibilityRng = SearchRng.Find(What:=CombinationToSearch, After:=SearchRng.Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole) If Not IncompatibilityRng Is Nothing Then Testcombinations = "Yes" Exit Function End If End If InCounter = InCounter + 1 Loop InCounter = 1 OutCounter = OutCounter + 1 Loop Testcombinations = "No" End Function
Then ALT + F11 to open the VBE Editor.
On Insert menu click on Module.
Then paste the code above.
Then in your workbook type =TestCombinations("Test reference holding the combination","Range with the incompatibilities").
For example in the attached workbook in cell K16 (row "Conflict") the formula should be =TestCombinations(K15,$B$17:$B$24).
Hope that helps.
_________________________________
Best Regards
Nicolas Michael
Thanks;
I tried it but it does not seem to work. Attached is the corrected file. When I change a cell to include incompatibilities there is no change in the formula result. I am not very familiar with macros but it seems as if it does not automatically run and has no code to start it?
You did everything OK re macros. Just change the formula as per attached workbook.
_________________________________
Best Regards
Nicolas Michael
Thank you so much, works perfectly!
You are welcome. Thanks for the feedback.
_________________________________
Best Regards
Nicolas Michael
Bookmarks