String Cleaning For Name Matching 1

[Felix18807]

I have created a module that removes/replaces part of a string for the purposes of client matching. This code is run against both existing client data and any incoming bulk imports to the client list to create a linking field (which in turn is used to help prevent client duplication).

The code looks a bit like this.....

Function ParString(StringIn As String) As String
On Error GoTo Error_Handler
Dim TempString As String

‘Start and tidy
TempString = StringIn
TempString = Trim(StringIn)

‘Direct Removal (Anywhere in string)
TempString = Replace(TempString, "REMOVETHIS", " ")

‘Direct Replacement  (Anywhere in string)
TempString = Replace(TempString, "REPLACETHIS", "WITHTHIS")

'Direct Removal (Start of String)
If Left(TempString, 5) = "REMOVESTART" Then TempString = Replace(TempString, " REMOVESTART ", "", , 1)

‘Direct Replacement (Start of String)
If Left(TempString, 11) = "REPLACETHIS " Then TempString = Replace(TempString, " REPLACETHIS ", "WITHTHIS ", , 1)

'Direct Removal (End of String)
If Right(TempString, 11) = "REPLACETHIS " Then TempString = Trim(Left(TempString, Len(TempString) - 11))

‘Direct Replacement (End of String)
If Right(TempString, 11) = "REPLACETHIS" Then TempString = Trim(Left(TempString, Len(TempString) - 11)) & “WITHTHIS”

' Remove from start loop
Do Until Left(TempString, 3) <> "Ms " And Left(TempString, 5) <> "Miss " And Left(TempString, 3) <> "Mr " And _
Left(TempString, 3) <> "Dr " And Left(TempString, 4) <> "Mrs " 

If Left(TempString, 3) = "Ms " Then TempString = Replace(TempString, "Ms ", "", , 1)
If Left(TempString, 5) = "Miss " Then TempString = Replace(TempString, "Miss ", "", , 1)
If Left(TempString, 3) = "Mr " Then TempString = Replace(TempString, "Mr ", "", , 1)
Loop

If Len(TempString) = 0 Then
ParString = ""
Exit Function
End If

'Merging Initials
Dim v, i
If TempString <> "" Then
    v = Split(TempString, " ")
    ParString = v(0)
    For i = LBound(v) To UBound(v) - 1
        If Len(v(i)) = 1 And Len(v(i + 1)) = 1 Then
            ParString = ParString & v(i + 1)
        Else
            ParString = ParString & " " & v(i + 1)
        End If
    Next i
End If

End Function

The main difference is that in the live code there are many REPLACE THIS with THAT lines.

What I am considering is having a table of REPLACETHIS with THATs which would allow me to select which part strings I want removed on the fly. As this function has to deal with large numbers of records I need it to be efficient. My question is - is there an efficient way of implementing the on the fly approach?

The only way I can think of is to load the REPLACETHIS with THAT table as a recordset and go through each line but I am not sure if this will work well.

Any advice, anecdotes or help would be very gratefully received.

 

[vbajock]

The answer to your question is yes, works great, use a table, sure, fine.