by John Thatcher (1 Submission)
Category: String Manipulation
Compatability: VB 6.0
Difficulty: Unknown Difficulty
Originally Published: Tue 26th August 2003
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
Normalize address information for mass mailing, mail merge, etc The 'Normalize function uses the 'Replace' and 'MySplit' functions.
VBcoders
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
No comments have been posted about Normalize address information for mass mailing, mail merge, etc The 'Normalize function uses the 'R. Why not be the first to post a comment about Normalize address information for mass mailing, mail merge, etc The 'Normalize function uses the 'R.
