CODEWITHSUNDEEP
vbaexcelpunctuation
stupidgal
stupidgal

Reputation: 171

Partial cell match and disregarding punctuation VBA

This is the extended question from Partial Cell Match.

I was wondering how I could further improve the following coding provided by John Coleman if there's punctuation in the name,

An example of Name1 would be "IT executive Sally, Lim"

An example of Name2 would be "Sally, Lim"

Name1 = Sheets("Work").Cells(RowName1, ColName1)
Name2 = Sheets("Roster").Cells(RowName2, ColName2)

If UCase(Trim(Name1)) Like "*" & UCase(Trim(Name2)) & "*" then
    Name2.Font.Strikethrough = True
    End If

Upvotes: 1

Views: 101

Answers (1)

SierraOscar
SierraOscar

Reputation: 17637

Use a function to "tidy" up the string:

Function ReplacePunct(strInput As String) As String

chars = Array(".", ",", ";", ":") '// Change as required

    For Each ch In chars
        While InStr(strInput)
            strInput = Replace(strInput, CStr(ch), vbNullString)
        Wend
    End If

End Function

Then use it like so:

If UCase(Trim(ReplacePunct(Name1))) Like "*" & UCase(Trim(ReplacePunct(Name2))) & "*" then

Upvotes: 2

Related Questions