• Home
 · 
• String Manipulation
 · 
• Normalize address information for mass mailing, mail merge, etc The 'Normalize function uses the 'R

Normalize address information for mass mailing, mail merge, etc The 'Normalize function uses the 'R

Normalize address information for mass mailing, mail merge, etc The 'Normalize function uses the 'Replace' and 'MySplit' functions.

Rate Normalize address information for mass mailing, mail merge, etc The 'Normalize function uses the 'R

• 1
• 2
• 3
• 4
• 5

(1(1 Vote))

Option Explicit

Public Function Replace(CheckString, ReplaceWhat, ReplaceValue)
    On Error GoTo TrapIt
    Dim TempString As String, TotalStringLength As Long, CharPos As Long, GoodLeftCount As Long
    Dim GoodLeftString As String, RightStringLength As Long, Remainder As String
    TempString = CheckString
    Do Until InStr(TempString, ReplaceWhat) = 0
        TotalStringLength = Len(TempString)
        CharPos = InStr(TempString, ReplaceWhat)
        GoodLeftCount = (CharPos - 1)
        GoodLeftString = Left(TempString, GoodLeftCount)
        RightStringLength = (TotalStringLength - CharPos)
        Remainder = Right(TempString, RightStringLength)
        TempString = GoodLeftString & ReplaceValue & Remainder
    Loop
    Replace = TempString
    Exit Function
TrapIt:
Replace = CheckString
End Function

Function MySplit(VariableToSplit As String, CharacterToSplitOn As String) As Variant
    Dim Splitter As String, MainString As String, Pos1 As Long, TempLeftString As String
    Dim StringArray() As Variant, Counter As Double
    MainString = VariableToSplit
    Splitter = CharacterToSplitOn
    Counter = 0
    Do Until Len(MainString) = 0
        Pos1 = InStr(MainString, Splitter)
        If Pos1 > 0 Then
            TempLeftString = Left(MainString, Pos1)
            TempLeftString = Replace(TempLeftString, Splitter, "")
            ReDim Preserve StringArray(Counter)
            StringArray(Counter) = Trim(TempLeftString)
            MainString = Right(MainString, (Len(MainString) - Pos1))
            Counter = Counter + 1
        Else
            ReDim Preserve StringArray(Counter)
            StringArray(Counter) = Trim(MainString)
            MainString = ""
        End If
    Loop
    MySplit = StringArray()
End Function

Public Function Normalize(StrToChange As String) As String
    Dim Temp1 As String, Temp2 As String, Temp3 As String, Temp4 As String
    Dim Temp As Variant, n As Long, FinStr As String, TestStr As String
    TestStr = "ne, nw, se, sw, po, rr, AA, AE, AK, AL, AP, AR, AS, AZ, CA, CO, DC, DE, FL, FM, GA, GU, HI, IA, ID, IL, IN, KS, KY, LA, MA, MD, ME, MH, MI, MN, MO, MP, MS, MT, NC, ND, NE, NH, NJ, NM, NV, NY, OH, OK, OR, PA, PR, PW, RI, SC, SD, TN, TX, UT, VA, VI, VT, WA, WI, WV, WY, apo"
    If Len(StrToChange) < 1 Then
        Normalize = ""
        Exit Function
    End If
    StrToChange = Replace(Replace(Replace(StrToChange, ".", ""), "#", ""), ",", "")
    Temp = MySplit(StrToChange, " ")
    FinStr = ""
    For n = 0 To UBound(Temp)
        If Temp(n) <> "" Then
            Temp1 = LCase(Temp(n))
            Temp2 = UCase(Left(Temp1, 1))
            Temp3 = Right(Temp1, Len(Temp1) - 1)
            Temp4 = Temp2 & Temp3
            If InStr(TestStr, Temp4) > 0 Then
                Temp4 = UCase(Temp4)
            Else
                Temp4 = Temp4
            End If
            FinStr = FinStr & Temp4 & " "
        End If
    Next n
    Normalize = Trim(FinStr)
End Function

Sub Test()
    MsgBox Normalize("john doe 324 nw 18th ct somewhereville, nd 74011")
End Sub