Splitting address fields into 3 separate fields

[Shirley]

Hi,
I'm trying to create a function to split up Ciy, State,
and Zip data from one field to 3 fields.

Here's the code:
If commapos <> 0 Then
city = Trim(Left$(address, commapos - 1))
length = Len(address)
'state = Right$(address, length - commapos)
state = Trim(Mid$(address, InStr(address, ",") + 1, 4))
zip = Trim(Right$(address, 5))

This code works if the state is in one word,
like "Florida", but not "New York"

Any suggestions?

Thanks

 

[John Spencer (MVP)]

Assuming commapos is the position of a comma in the address string.


If commapos <>0 then
city = Trim(Left$(address, commapos - 1))
State = Mid(Address,commapos +1)
State = Trim(Left(State,Len(State)-5))
Zip = Trim(Right(Address,5))
End if