Parse data (City, ST, Zip) (1 Viewer)

[scgoodman]

Have a destination&city field in access and need this to be parsed into (3) fields. Right now I have Dest city, Dest st and Dest zip as columns. I am not sure how to trim or instr what I need?

INGLEWOOD, CA 90303

Please help.
Shannon

 

[jdraw]

Have a destination&city field in access and need this to be parsed into (3) fields. Right now I have Dest city, Dest st and Dest zip as columns. I am not sure how to trim or instr what I need?

INGLEWOOD, CA 90303

Please help.
Shannon

What have you tried so far?

 

[DCrake]

Have a look a the Split() function and use a space for the delimiter. migth be a problem though for cities like New York. If you can guarantee that there is a comma between them then you could use this a the delimiter.

other solution:

Get the 3rd element

Public Function GetZip(Anystring As String) As String
'Gets everything after the last space in the string

GetZip = Mid(AnyString,InStrRev(AnyString," ")+1)

End Function

Get 2nd Element

Public Function GetST(anyString As String) As String

'Strips out any commas
AnyString = Replace(AnyString,",","")
'Gets the 2 digit state code before the last space in the string
GetST = Mid(AnyString,InStrRev(AnyString," ")-2,2)

End Function

Get 1st Element - this is the tricky one

Public Function GetCity(AnyString As String) As String
'Needs to content with spaces in the city name

'First strip out any commas

AnyString = Replace(AnyString,",","")

Dim Marker As Integer

'Where is the position of the next to last space in the string
'Should be 3 spaces to the right of the last space
Marker = InStrRev(AnyString," ")-3

GetCity = Left(AnyString,Marker)

End Function

All above code is untested aircode, but should work

How to use:

Create a query and insert three columns

City:GetCity([UnparsedField])
State:GetST([UnparsedField])
Zipcode:GetZip([UnparsedField])

open in datasheet view.

David