Character string search

**Kwells** · 2012-10-30, 06:29 PM

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

**michaelnicolas** · 2012-11-01, 02:03 PM

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

**Kwells** · 2012-11-01, 04:49 PM

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".

**michaelnicolas** · 2012-11-02, 08:50 AM

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.

**Kwells** · 2012-11-02, 01:48 PM

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?