Searching for a substring with a fixed end

Question

I would like to search for a substring that ends after a certain phrase within a collection (listed in an Excel column) of other strings.

So let's say I want to search for the string "BLUE MOON" within other strings and I want to determine that the substring ends after the "N" of "BLUE MOON" in order to avoid the result TRUE in case of e.g. "BLUE MOONLIGHT". In other words what I need is a search for any parts of a word, but only lefthand. Righthand there should be a fixed border, that is zero additional characters. On the other hand, I need positive results if the beginning is different, for example "DARK BLUE MOON" should result in TRUE. Therefore complete equality is not an option.

I'd like to use Find but I believe it is not possible. Finddoes not seem to accept any wildcards besides *.

Here are some Words for you to test:

1. Positive result of search expected:

   BLUE MOON
   DARK BLUE MOON
   LIGHT BLUE MOON
   

2. Negative result of search expected::

   BLUE MOONLIGHT
   LAST BLUE MOONSHINE
   BLUE MOONDANCE
   

Any hints are also appreciated. Right now I am using the following function to delete words (works fine, except it also deletes the previously mentioned cases with negative expected search outcome):

Sub testingXXX()

Dim ws As Worksheet
Set ws = ActiveWorkbook.ActiveSheet

Dim aCell As Range, bCell As Range, aSave As String, y As Long

MyAr = Split("*BLUE MOON", ",")

 For y = LBound(MyAr) To UBound(MyAr)
      With ws
         Set aCell = .Columns(1).Find(what:=MyAr(y), LookIn:=xlValues, _
                          lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                          MatchCase:=False, SearchFormat:=False)

         If Not aCell Is Nothing Then
             aSave = aCell.Address
             Do
                 If bCell Is Nothing Then
                     Set bCell = .Range("A" & aCell.row)
                 Else
                     Set bCell = Union(bCell, .Range("A" & aCell.row))
                 End If

                 Set aCell = .Columns(1).FindNext(after:=aCell)

             Loop Until aCell.Address = aSave
         End If

         Set aCell = Nothing
     End With

 Next y

 If Not bCell Is Nothing Then bCell.EntireRow.Delete

End Sub

Answer 1

This is why regexes were invented. If you use \b to specify a word border, only the exact phrase "BLUE MOON" will be matched with no additional characters allowed. The following will match strings containing "BLUE MOON" anywhere within:

Const PHRASES As String = "BLUE MOON,DARK BLUE MOON,LIGHT BLUE MOON,BLUE MOONLIGHT,LAST BLUE MOONSHINE,BLUE MOONDANCE"

Dim re
Set re = CreateObject("VBScript.RegExp")
re.Pattern = "\bBLUE MOON\b"

Dim w
For Each w In Split(PHRASES, ",")

    If re.Test(w) Then
        Debug.Print w & " = Match"
    Else
        Debug.Print w & " = No match"
    End If

Next

Output (aligned for readability):

BLUE MOON           = Match
DARK BLUE MOON      = Match
LIGHT BLUE MOON     = Match
BLUE MOONLIGHT      = No match
LAST BLUE MOONSHINE = No match
BLUE MOONDANCE      = No match

If you want to make sure the string ends after "MOON" with absolutely nothing additional, include the end-of-string anchor ($) in your pattern instead:

re.Pattern = "\bBLUE MOON$"