look for a word within a sentence

Question

I'm trying to look for a word within a sentence, but I would like to find only the instances where it appears as its own word and not when it's only part of a word.

For example, I'm looking for ''em'' (Which is short for espresso machine).

As of now, the code will pick up sentences that have the words: emergency, premium, them, removed, problem, system, remote, temp, empty etc'.

On the other hand, instances of ''em'' that I would like to grab have different characters before or after them, something like: >em or em- .

Thank you so much for your help

here is an example of the code I am using:

 Sub Macro5()
    Dim row As Integer
    row = 1
    Dim word As String
    word = "em"
    Do While Sheets("11").Cells(row, 1).Text <> ""
        Dim temp As String
        temp = Sheets("11").Cells(row, 1).Text
        If InStr(1, temp, " " + word + " ", vbTextCompare) Or InStr(1, temp, " " + word, vbTextCompare) Then
            Sheets("11").Cells(row, 14) = "True"
        Else
            Sheets("11").Cells(row, 14) = "False"
        End If
        row = row + 1
    Loop
End Sub

Well you could create an array with each word by spliting on each space. Then check if the word your looking for is in the array. — MadeInDreams, Jan 01 '22 at 19:06
One simple way would be to replace all punctuation with a special character, like vertical bar (|), also add vertical bars before and after the text, do the same on the search word (and maybe upper/lowercase each), and then search, like: `instr( "|" & replace(replace(replace("sentences that have the words: emergency, premium, them, em"," ","|"),",","|"),":","") & "|", "|"&"em"&"|")` If the calls to replace gets tedious you can write a function like: https://stackoverflow.com/questions/15723672/how-to-remove-all-non-alphanumeric-characters-from-a-string-except-period-and-sp — tinazmu, Jan 02 '22 at 01:04

VBasic2008 · Answer 1 · 2022-01-03T13:37:36.717

Find a Word in a Sentence

Option Explicit

' In Excel, in cell 'N2', you could use the formula '=IsWordInSentence(A2,"em")'
' and copy down.

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      For each value in the 'occupied' cells of column 'A',
'               starting from the second row, of worksheet '11'
'               of the workbook containing this code ('ThisWorkbook'),
'               returns a boolean, indicating whether it contains the word 'em',
'               in the same row of column 'N'.
' Calls:        IsWordInSentence
'                   StrNonAlphaNumericSpaceReplaced
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub Macro5()
    
    Const wsName As String = "11"
    Const fRow As Long = 2
    Const sCol As String = "A"
    Const dCol As String = "N"
    Const Word As String = "em"
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Worksheets(wsName)
    Dim lRow As Long: lRow = ws.Cells(ws.Rows.Count, sCol).End(xlUp).row
    If lRow < fRow Then Exit Sub ' no data
    Dim srg As Range
    Set srg = ws.Range(ws.Cells(fRow, sCol), ws.Cells(lRow, sCol))
    Dim drg As Range: Set drg = srg.EntireRow.Columns(dCol)
    drg.Formula = "=IsWordInSentence(" & srg.Cells(1).Address(0, 0) _
        & ",""" & Word & """)"
    drg.Value = drg.Value ' out-comment this line to keep formulas
        
End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Returns a boolean indicating if a word ('Word') can be found
'               in another string ('Sentence'). A word is defined by any
'               number of consecutive alpha-numeric characters.
' Calls:        StrNonAlphaNumericSpaceReplaced.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function IsWordInSentence( _
    ByVal Sentence As String, _
    ByVal Word As String) _
As Boolean
    If Len(Sentence) = 0 Then Exit Function
    If Len(Word) = 0 Then Exit Function
    
    Dim SearchString As String
    SearchString = StrNonAlphaNumericSpaceReplaced(Sentence)
    
    Dim sArr() As String: sArr = Split(SearchString)
    
    IsWordInSentence = IsNumeric(Application.Match(Word, sArr, 0))

End Function

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Returns a string generated by replacing each non-alpha-numeric
'               character in a given string with a space.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function StrNonAlphaNumericSpaceReplaced( _
    ByVal Sentence As String) _
As String
    
    Dim OldChar As String
    Dim NewString As String
    Dim n As Long
    
    For n = 1 To Len(Sentence)
        OldChar = Mid(Sentence, n, 1)
        If OldChar Like "[A-Za-z0-9 ]" Then
            NewString = NewString & OldChar
        Else
            NewString = NewString & " "
        End If
    Next n
    
    StrNonAlphaNumericSpaceReplaced = NewString
    
End Function

look for a word within a sentence

1 Answers1

Find a Word in a Sentence