<co*********@gmail.comwrote in message
news:11*********************@b28g2000cwb.googlegro ups.com...
Hi:
Can any one please tell me how do i separate city state and zip. I ask
this question in previous post. But when the city name is in two words
it mess up my values otherwise it works.
CityStateZip
-------------
Las Vegas NA, 12345
Oradell NJ, 07649
Bay Shore NY, 11703
The following very-lightly-tested code seems to work fine on the samples you
gave (just for the record, there's no state with the abbreviation "NA").
Function SplitAdd(pstrAddr As String) As Boolean
On Error GoTo Proc_Error
Dim strZIP As String
Dim strST As String
Dim strCity As String
SplitAddr = False 'Preset for "fail" return
strZIP = Right(pstrAddr, Len(pstrAddr) - InStrRev(pstrAddr, ", ") - 1)
Debug.Print strZIP
strST = Mid(pstrAddr, InStrRev(pstrAddr, ", ") - 2, 2)
Debug.Print strST
strCity = Left(pstrAddr, InStrRev(pstrAddr, ", ") - 3)
Debug.Print strCity
SplitAddr = True 'Set for "success" return
Proc_Exit:
On Error GoTo 0
Exit Function
Proc_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
SplitAdd of Module TestModule1"
Resume Proc_Exit
End Function
You'll likely want to change it to pass the variables into which to return
the separate parts of the address, and there's no particular reason for
having it as a Function instead of a Sub procedures (it was simple to test
as a function)
