• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 495
  • Last Modified:

VB: Check if a string contains a specific word

Using VB on an Access Table, I want to select some field if the field value which is a string of 3 or 4 words has the third word or the fourth word be the word "Spot".  How do I check this?

One way may be to find the second blank space character, take the Right of that to crop out that space and everything before that and then do a Like "Spot*"?

What would be good VB code for this?

1 Solution
Lee W, MVPTechnology and Business Process AdvisorCommented:
Perhaps some combination using INSTR?

Or perhaps SPLIT the string into a variable array and check the 3rd and 4th variables for that string?
IF Instr(yourStr,"TheWord") > 0 then
  msgbox "Itsthere"
 msgbox "Not there"
end if
Will this help you any, Copy and paste, Call the Function IsSpotThere with the sentence to check
It will return the value below, just check this value.


Function IsSpotThere(strWord) As Integer

'returns 0 if Spot is not in the 3rd or 4th Word
'returns 1 if Spot is in the 3rd Word
'returns 2 if Spot is in the 4rd Word
'returns 3 if Spot is in the 3rd and 4th Word

Dim Arr_Word

' Remember Split is Zero Based
Arr_Word = Split(strWord, " ")

IsSpotThere = 0

If UBound(Arr_Word) > 1 Then If InStr(1, UCase(Arr_Word(2)), "SPOT") > 0 Then IsSpotThere = 1
If UBound(Arr_Word) > 2 Then If InStr(1, UCase(Arr_Word(3)), "SPOT") > 0 Then IsSpotThere = IsSpotThere + 2
End Function

Here is an example using Split

    Dim sText As String
    Dim sFind As String
    Dim iWord As Integer
    sText = "Now is the time for all good men to come to the aid of their country."
    sFind = "the"
    If MatchWord(sText, sFind, 4) Then
        MsgBox "found word"
    End If

Function MatchWord(sText As String, sFind As String, iWord As Integer) As Integer
    Dim sWord() As String
    Dim iFound As Integer
    sWord = Split(sText, " ")
    iFound = False
    Select Case iWord - 1
        Case 0 To UBound(sWord)
            iFound = CBool(UCase$(Trim$(sWord(iWord - 1))) = UCase$(Trim$(sFind)))
    End Select
    MatchWord = iFound
End Function


Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now