CODEWITHSUNDEEP
excelvba
Linga
Linga

Reputation: 955

Finding word in a sentence

I'm finding a word in a cell which has sentence, both sentence & the word to be found may have space/special character.

But the function or macro should ignore them & match it if the word exits given below is the example.

Column1	         Column2	           Result	Expected Result
Spider-Man	     SpiderMan 56	       TRUE	    TRUE
6x	             6x25	               TRUE	    TRUE
jesse james	    jesse/james	           TRUE	    TRUE
13.3"	        133 hd	               FALSE	TRUE
15.6"	        5517 156 ccfl	       FALSE	TRUE
United States	United States Brands   FALSE	TRUE
United States	UnitedStates Brands	   FALSE	TRUE
United States	United-States Brands   FALSE	TRUE
Force	        Air "Force" One	       FALSE	TRUE
Force	        Air Force-One	       FALSE	TRUE

In the above example I'm working with below functions but not yet getting the desired result.

Function ExactString(Text As String, Word As String) As Boolean
a = Module1.StripNonAlpha(Text)
b = Module1.StripNonAlpha(Word)
     'ExactString = " " & UCase(a) & " " Like "*[!A-Z]" & UCase(b) & "[!A-Z]*"
     If InStr(1, a, b, 1) Then
     ExactString = True
     Else
     ExactString = False
     End If
End Function
-----------------------------------------------------------------
Function StripNonAlpha(TextToReplace As String) As String
Dim ObjRegex As Object

Set ObjRegex = CreateObject("vbscript.regexp")
With ObjRegex
.Global = True
.Pattern = "[^a-zA-Z\s]+"
StripNonAlpha = .Replace(Replace(TextToReplace, "-", Chr(32)), vbNullString)
StripNonAlpha = Module1.CleanSpace(StripNonAlpha)
End With
End Function
----------------------------------------------------------------
Function CleanSpace(ByVal strIn As String) As String
    strIn = Trim(strIn)

  ' // Replace all space pairings with single spaces
    Do While InStr(strIn, " ")
        strIn = Replace(strIn, " ", "")
        strIn = Replace(strIn, "  ", "")
    Loop

    CleanSpace = strIn
End Function

Is there any other way to achieve my target?

Upvotes: 0

Views: 162

Answers (1)

JNevill
JNevill

Reputation: 50034

Change the REGEX in the second function to also allow numbers and to remove spaces, since that seems important for your case. You can remove the third function since it's redundant.

Function StripNonAlpha(TextToReplace As String) As String
    Dim ObjRegex As Object

    Set ObjRegex = CreateObject("vbscript.regexp")
    With ObjRegex
        .Global = True
        .Pattern = "[^0-9a-zA-Z]+"
        StripNonAlpha = .Replace(TextToReplace, vbNullString)
    End With
End Function

You could also remove your first function since it can be easily handled by worksheet formulas, which should have less overhead. In your sheet, assuming Column A and Column B are the two you are comparing, then in Column C:

=NOT(ISERROR(FIND(StripNonAlpha(A1),StripNonAlpha(B1))))

Upvotes: 1

Related Questions