1. Character string search

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

2. Can you be a bit more specific with a few examples in a worksheet?

3. Character String search

Originally Posted by michaelnicolas
Can you be a bit more specific with a few examples in a worksheet?
I am attaching the worksheet. The formula I am having difficulty with is in row 16 columns D thru AY. An example of it working correctly is in cell R16 and incorrectly in cell K16. In K16 both characters G and I exist and I want the formula to resolve to "Yes".

4. Test this UDF:
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```
Open your workbook, then save it as "Excel Macro Enabled Workbook".
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.

5. 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?

6. You did everything OK re macros. Just change the formula as per attached workbook.

7. Thank you so much, works perfectly!

8. You are welcome. Thanks for the feedback.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•