you should get a copy of neatcode, it's available from the MS website. it is an mdb with lots of handy stuff. if you can't find it, let me know and i will try to find the URL.
here is the code from neatcode97:
------------------------------
Sub ParseCSZ(ByVal S As String, City As String, State As String, Zip As String)
'
' Parses address "New York NY 00123" into separate fields.
' Supports the following formats:
' New York NY 12345-9876
' Pierre, North Dakota 45678-7654
' San Diego, CA, 98765-4321
'
' Words are extracted in the following order if no commas are found to delimit the values:
' Zip, State, City
'
Dim P As Integer
'
' Check for comma after city name
'
P = InStr(S, ",")
If P > 0 Then
City = Trim$(left$(S, P - 1))
S = Trim$(Mid$(S, P + 1))
'
' Check for comma after state
'
P = InStr(S, ",")
If P > 0 Then
State = Trim$(left$(S, P - 1))
Zip = Trim$(Mid$(S, P + 1))
Else ' No comma between state and zip
Zip = CutLastWord(S, S)
State = S
End If
Else ' No commas between city, state, or zip
Zip = CutLastWord(S, S)
State = CutLastWord(S, S)
City = S
End If
'
' Clean up any dangling commas
'
If Right$(State, 1) = "," Then
State = left$(State, Len(State) - 1)
End If
If Right$(City, 1) = "," Then
City = left$(City, Len(City) - 1)
End If
End Sub
----------------------------------------------------------------
think this will probably suit your needs...you may need to do a left() on the string zip
if you only want 5 characters...
hth,
al
