Match Complete word in string

Question

I have the below code which populates an offset cell with service should the criteria match. However at this stage, it operates on the basis that should the string appear within a cell, it will apply the value rather than the word being a complete word. For example I have "Visit" within my criteria and "Vistor" (within a cell) will comply within my code when it shouldn't. Please can someone assist?

Option Compare Text
Sub CHECK_CELL_VALUES()
    Dim LASTROW As Long
    Application.ScreenUpdating = False
        With Sheet1
        LASTROW = .Range("A1048576").End(xlUp).Row


            For i = 2 To LASTROW
                If Cells(i, 7).Value Like "*Service*" _
                Or Cells(i, 7).Value Like "*Servicing*" _
                Or Cells(i, 7).Value Like "* Labour*" _
                Or Cells(i, 7).Value Like "* Job*" _
                Or Cells(i, 7).Value Like "* Hire*" _
                Or Cells(i, 7).Value Like "* Visit*" _
                Or Cells(i, 7).Value Like "* Scaffold*" _
                Or Cells(i, 7).Value Like "* Contract*" _
                Or Cells(i, 7).Value Like "* Hour*" _
                Or Cells(i, 7).Value Like "* Month*" _
                Or Cells(i, 7).Value Like "* Quarter*" _
                Or Cells(i, 7).Value Like "* Day*" _
                Or Cells(i, 7).Value Like "* Maintenance*" _
                Or Cells(i, 7).Value Like "* Repair*" _
                Or Cells(i, 7).Value Like "* Survey*" _
                Or Cells(i, 7).Value Like "* Training*" _
                Or Cells(i, 7).Value Like "* Calibration*" _
                Then Cells(i, 7).Offset(0, 46).Value = "Service"
                Debug.Print Cells(i, 7).Address
            Next i

        End With
    Application.ScreenUpdating = True
End Sub

I also endorse @Trum suggestion that if you want to match such situation keep your criteria word surrounded by a space like " Visit " — skkakkar, Sep 28 '15 at 10:30
@Trum The use of space alone to define a word causes an issue if the word is at the beginning or end of a line; or if the word is "ended" with an apostrophe or quote mark. That may not be an issue for the original poster. But, in general, one could test for any non-word character, or the beginning or end of the string, in order to define a word. This can be done with "straight VBA", and is also easily done using Regular Expressions which has a word boundary token. — Ron Rosenfeld, Sep 28 '15 at 18:55

score 0 · Answer 1 · answered Jun 01 '16 at 16:45

The way i do this is: a) to add a space before and after the string to be searched b) search for the string with a space added before and after. here's an example that searches for the word VISIT and it would match "VISIT" "A VISIT" "VISIT SOMETHING" but will exclude "VISITOR"

InStr(1, " " & UCase(Sheets("Sheet1").Cells(i, 1)) & " ", " VISIT ")

score -1 · Answer 2 · answered Sep 28 '15 at 14:43

-1

Try changing this:

If Cells(i, 7).Value Like "*Service*"

to this:

If InStr(Cells(i,7).Value,"Service") > 0

And so on and so forth - this will match the entire string "Service" More info on InStr can be found here

answered Sep 28 '15 at 14:43

mozgov_net

317
2
12

Your code will still find the fragment within a larger word. e.g. `Instr("Visitor","Visit")` will return a value > 0. – Ron Rosenfeld Sep 28 '15 at 15:37

Match Complete word in string

2 Answers2