Character string search

Kwells

New member
Joined
Oct 30, 2012
Messages
4
Reaction score
0
Points
0
Location
Colorado
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
 

Kwells

New member
Joined
Oct 30, 2012
Messages
4
Reaction score
0
Points
0
Location
Colorado
Character String search

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

Attachments

  • Character string search sample.xlsx
    15.5 KB · Views: 14

michaelnicolas

New member
Joined
Apr 19, 2012
Messages
11
Reaction score
0
Points
0
Location
Nicosia, Cyprus
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

New member
Joined
Oct 30, 2012
Messages
4
Reaction score
0
Points
0
Location
Colorado
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?
 

Attachments

  • Character string search sample.xlsm
    19.7 KB · Views: 10

michaelnicolas

New member
Joined
Apr 19, 2012
Messages
11
Reaction score
0
Points
0
Location
Nicosia, Cyprus
You did everything OK re macros. Just change the formula as per attached workbook.
 

Attachments

  • Character string search sample.xlsm
    19.7 KB · Views: 10
Top