How to do a proper keyword search

Question

If I search for the term 'tfo' in the cell value 'TFO_xyz' then the result should be TRUE. If I search for the term 'tfo' in the cell value 'TFO systems' then the result should be TRUE.

If I search for the term 'tfo' in the cell value 'spring TFO' then the result should be TRUE.

BUT if I check 'tfo' in the cell value 'Platform' then I want the result as FALSE

I have used the formula =IF(COUNTIF(A2,"*tfo*"),"TRUE","FALSE"), but this wont give result as FALSE when I check 'tfo' in the word 'Platform'

NOTE:

Platform should be false because tfo is coming in between a word. I'm looking result as True for cell values with just the word tfo like in tfo<‌space>America or TFO_America or <‌space>TFO systems. But I want FALSE result for the words Platform and portfolio because in these two words the term tfo comes in between alphabets.

Answer 1

Try this:

Dim x As Long: x = 1

With Sheet1

    Do While x <= .Cells(.Rows.Count, 1).End(xlUp).Row

        If VBA.Left(.Cells(x, 1).Value, 3) = "tfo" Or VBA.Right(.Cells(x, 1).Value, 3) = "tfo" Then

            .Cells(x, 2).Value = True

        End If

        x = x + 1

    Loop

End With

Answer 2

I see two dimensions of complexity in your question:

• Where does the key word occur in the text (beginning, middle, end)
• What are the characters that separate words.

The first one is fixed size, you need to handle three cases. The second one depends on the number of characters you want to accept as delimiters. Below I assumed that you accept space and underscore, however, you may expand this set by inserting more SUBSTITUTE function calls.

In my table, $A2 is the cell in which you search for the keyword, while B$1 contains the keyword.

To standardize the separator character, you need the formula:

B2=SUBSTITUTE($A2,"_"," ")

To check if the string starts with the keyword:

C2=--(LEFT($B2,LEN(B$1)+1)=B$1&" ")

To check if the string ends with the keyword:

D2=--(RIGHT($B2,LEN(B$1)+1)=" "&B$1)

To check if the keyword is in the middle of the string:

E2=--(LEN(SUBSTITUTE(UPPER($B2)," "&UPPER(B$1)&" ",""))<LEN($B2))

To evaluate the above three cases:

F2=--(0<$C2+$D2+$E2)

If you want to use a single cell, combine the formulas into:

G2=--(0<--(LEFT(SUBSTITUTE($A2,"_"," "),LEN(B$1)+1)=B$1&" ")+--(RIGHT(SUBSTITUTE($A2,"_"," "),LEN(B$1)+1)=" "&B$1)+--(LEN(SUBSTITUTE(UPPER(SUBSTITUTE($A2,"_"," "))," "&UPPER(B$1)&" ",""))<LEN(SUBSTITUTE($A2,"_"," "))))

It is not very readable in the end but I don't think there was an easier solution using Formulas only.

Note: If you want to modify the set of characters accepted as delimiters, add more SUBSTITUTE function calls to B2, then copy the Formula of F2 into notepad and replace $C2 with the formula of C2, etc., then replace $B2 with the updated Formula of B2.

Update

Building on the idea in Ron Rosenfelds comment to tigeravatar's answer, the formula can be simplified (the beginning, middle, ending cases can be joined):

=--(LEN(SUBSTITUTE(" "&UPPER($B2)&" "," "&UPPER(B$1)&" ",""))<LEN($B2))

After substituting $B2 with its formula:

=--(LEN(SUBSTITUTE(" "&UPPER(SUBSTITUTE($A2,"_"," "))&" "," "&UPPER(B$1)&" ",""))<LEN(SUBSTITUTE($A2,"_"," ")))

Answer 3

Try this formula. This assumes that word tfo will be at the beginning or end Just make sure to place appropriate cell names where i have 'A2' in the formula

=IF(OR(PROPER(LEFT(A2,3))="tfo",PROPER(RIGHT(A2,3))="tfo"),TRUE,FALSE)

Test Cases Below:

Answer 4

This formula will return true if TFO is at the beginning or end of any given word, or by itself, in the text string. It also checks every word in the text string, so TFO can be at beginning, middle, or end. The formula assumes that if a word starts or ends with TFO, then the result should be TRUE (as is the case for tfoAmerica so same rule would apply to tform), else FALSE.

=OR(ISNUMBER(SEARCH({" tfo","tfo "}," "&SUBSTITUTE(A2,"_"," ")&" ")))

Here are its results:

EDIT:

In the event that the result should only be TRUE if TFO is found by itself, then this version of the formula will suffice:

=ISNUMBER(SEARCH(" tfo "," "&SUBSTITUTE(A2,"_"," ")&" "))

Image showing results of second version:

Answer 5

My suggestion is to spend sometime to know your data and create a white-list. Since there is no easy way to properly do fuzzy search in strings.

Function TFO_Search(strText As String) As Boolean

Dim ArryString      As Variant
Dim ArryWhitelist   As Variant

' Create a White-List Array
ArryWhitelist = Array("TFO_", "TFO ", "_TFO", " TFO", "tfoAmerica")

For Each ArryString In ArryWhitelist
If InStr(UCase(strText), UCase(ArryString)) > 0 Then  'force to UPPER CASE
    TFO_Search = True
    Exit Function
Else
    TFO_Search = False
End If
Next

End Function

Answer 6

If you can rely on VBA, then regex is a more flexible solution.

There is a good summary, of how to use them in VBA: How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

For your keyword search problem I wrote the following:

Option Explicit

' Include: Tools > References > Microsoft VBScript Regular Expressions 5.5 (C:\Windows\SysWOW64\vbscript.dll\3)


Public Function SearchKeyWord(strHay As String, strNail As String, Optional strDelimiters As String = " _,.;/", Optional lngNthOccurrence As Long = 1) As Long ' Returns 1-based index of nth occurrence or 0 if not found
    Dim strPattern As String: strPattern = CreatePattern(strNail, strDelimiters)
    Dim rgxKeyWord As RegExp: Set rgxKeyWord = CreateRegex(strPattern, True)
    Dim mtcResult As MatchCollection: Set mtcResult = rgxKeyWord.Execute(strHay)
    If (0 <= lngNthOccurrence - 1) And (lngNthOccurrence - 1 < mtcResult.Count) Then
        Dim mthResult As Match: Set mthResult = mtcResult(lngNthOccurrence - 1)
        SearchKeyWord = mthResult.FirstIndex + Len(mthResult.SubMatches(0)) + 1
    Else
        SearchKeyWord = 0
    End If
End Function

Private Function CreateRegex(strPattern As String, Optional blnIgnoreCase As Boolean = False, Optional blnMultiLine As Boolean = True, Optional blnGlobal As Boolean = True) As RegExp
    Dim rgxResult As RegExp: Set rgxResult = New RegExp
    With rgxResult
        .Pattern = strPattern
        .IgnoreCase = blnIgnoreCase
        .MultiLine = blnMultiLine
        .Global = blnGlobal
    End With
    Set CreateRegex = rgxResult
End Function

Private Function CreatePattern(strNail As String, strDelimiters As String) As String
    Dim strDelimitersEscaped As String: strDelimitersEscaped = RegexEscape(strDelimiters)
    Dim strPattern As String: strPattern = "(^|[" & strDelimitersEscaped & "]+)(" & RegexEscape(strNail) & ")($|[" & strDelimitersEscaped & "]+)"
    CreatePattern = strPattern
End Function

Private Function RegexEscape(strOriginal As String) As String
    Dim strEscaped As String: strEscaped = vbNullString
    Dim i As Long: For i = 1 To Len(strOriginal)
        Dim strChar As String: strChar = Mid(strOriginal, i, 1)
        Select Case strChar
        Case ".", "$", "^", "{", "[", "(", "|", ")", "*", "+", "?", "\"
            strEscaped = strEscaped & "\" & strChar
        Case Else
            strEscaped = strEscaped & strChar
        End Select
    Next i
    RegexEscape = strEscaped
End Function

Once you have the above in a Module, you can insert formulas like the following:

=SearchKeyWord($A1,"tfo")

where A1 contains e.g. "tfo America".

As a third parameter, you may specify, which characters you want to treat as delimiters, by default they are space, underscore, comma, dot, semicolon and slash.

The return value is the position of the nth occurrence of the keyword, where n is the value of the fourth parameter (default: 1), or 0 if not found.

To check if the keyword is present in A1, compare the result to 0, which means not found:

=--(SearchKeyWord($A1,"tfo")<>0)