CODEWITHSUNDEEP
vbaexcelexcel-2013
IcyPopTarts
IcyPopTarts

Reputation: 504

Match Individual Words In String In VBA

Excel 2013 here - and am attempting to match the value in cell D to the value in cell C. The part that is leaving me pulling my hair out, is the fact that if a single word exists in column C it should be removed from column D.

For example

Column C          Column D
Red Hairy Hats    Hairy Cowpies

Since both fields contain the word Hairy it should be updated to read like so

Column C          Column D
Red Hairy Hats    Cowpies

I can not uncover how to do a wildcard match on string comparison in Excel VBA. I have this syntax which does an Exact match, but how could I do single words from the string like in my example above?

Dim i As Long
Dim resArry
dataArry = Cells(1).CurrentRegion
ReDim resArry(UBound(dataArry, 1) - 1, 1)
For i = 2 To UBound(dataArry, 1)
    If InStr(1, dataArry(i, 3), dataArry(i, 4), vbBinaryCompare) Then
        resArry(i - 2, 0) = ""
    Else
        resArry(i - 2, 0) = dataArry(i, 4)
    End If
Next

Range("D2").Resize(UBound(resArry, 1)) = resArry

Upvotes: 1

Views: 2470

Answers (3)

brettdj
brettdj

Reputation: 55682

A RegExp option with variant arrays.

Create a pattern for each C string against each D string for a whole word only replacement

\b(Red|Hairy|Hats)\b

etc

Sub Interesting()
Dim rng1 As Range
Dim X, Y
Dim lngCnt As Long
Dim ObjRegex As Object

Set rng1 = Range([c1], Cells(Rows.Count, "c").End(xlUp))
X = rng1.Value2
Y = rng1.Offset(0, 1).Value2
Set ObjRegex = CreateObject("vbscript.regexp")
With ObjRegex
.Global = True
For lngCnt = 1 To UBound(X, 1)
    .Pattern = "\b(" & Join(Split(X(lngCnt, 1), Chr(32)), "|") & ")\b"
    Y(lngCnt, 1) = .Replace(Y(lngCnt, 1), vbNullString)
    Next
End With

rng1.Offset(0, 1).Value2 = Y

End Sub

Upvotes: 1

Variatus
Variatus

Reputation: 14373

Private Sub Test()
    Dim C As String, D As String
    C = "Red Hairy Hats"
    D = "hairy cowpies"
    Debug.Print RemoveMatches(C, D)
End Sub

Private Function RemoveMatches(C As String, D As String) As String
    Dim Sp() As String
    Dim i As Integer

    Sp = Split(C)
    For i = 0 To UBound(Sp)
        If InStr(1, D, Sp(i), vbTextCompare) Then
            D = Trim(Replace(D, Sp(i), "", Compare:=vbTextCompare))
        End If
    Next i
    RemoveMatches = D
End Function

Upvotes: 0

Manngo
Manngo

Reputation: 16281

This is not a complete answer, since I’m a bit rusty with VBA, but rather than use instr to look for matches, you might have more success splitting both strings into arrays.

The process would be something like this:

  • split both strings using space
  • for each element in the second array
    • test whether it’s in the first array
    • if it is, remove the element
  • Join the second array back into a string using spaces
  • Repeat and rinse

Upvotes: 0

Related Questions