Results 1 to 8 of 8

Thread: Character string search

  1. #1
    Neophyte Kwells's Avatar
    Join Date
    Oct 2012
    Location
    Colorado
    Posts
    4
    Articles
    0

    Character string search



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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. #2
    Seeker michaelnicolas's Avatar
    Join Date
    Apr 2012
    Location
    Nicosia, Cyprus
    Posts
    11
    Articles
    0
    Can you be a bit more specific with a few examples in a worksheet?
    _________________________________
    Best Regards
    Nicolas Michael

  3. #3
    Neophyte Kwells's Avatar
    Join Date
    Oct 2012
    Location
    Colorado
    Posts
    4
    Articles
    0

    Character String search

    Quote Originally Posted by michaelnicolas View Post
    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".
    Attached Files Attached Files

  4. #4
    Seeker michaelnicolas's Avatar
    Join Date
    Apr 2012
    Location
    Nicosia, Cyprus
    Posts
    11
    Articles
    0
    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.
    _________________________________
    Best Regards
    Nicolas Michael

  5. #5
    Neophyte Kwells's Avatar
    Join Date
    Oct 2012
    Location
    Colorado
    Posts
    4
    Articles
    0
    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?
    Attached Files Attached Files

  6. #6
    Seeker michaelnicolas's Avatar
    Join Date
    Apr 2012
    Location
    Nicosia, Cyprus
    Posts
    11
    Articles
    0
    You did everything OK re macros. Just change the formula as per attached workbook.
    Attached Files Attached Files
    _________________________________
    Best Regards
    Nicolas Michael

  7. #7
    Neophyte Kwells's Avatar
    Join Date
    Oct 2012
    Location
    Colorado
    Posts
    4
    Articles
    0
    Thank you so much, works perfectly!

  8. #8
    Seeker michaelnicolas's Avatar
    Join Date
    Apr 2012
    Location
    Nicosia, Cyprus
    Posts
    11
    Articles
    0
    You are welcome. Thanks for the feedback.
    _________________________________
    Best Regards
    Nicolas Michael

Posting Permissions

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