How to parse CITY ST ZIP+/-4

I have a bunch of what you see below in a field
named CSZ. I want to parse it the easiest way
possible. I just don't know where to start...

CSZ
Anchorage AK 99518-3051
Anchorage AK 99518-3051
Juneau AK 99801-6917
Palmer AK 99645
Kenai AK 99611-1753
Fairbanks AK 99709-5008
Ketchikan AK 99901-1291
Smiths Station AL 36877-3700
Florence AL 35631-0419
Gadsden AL 35901-3006
Huntsville AL 35805-4106
Scottsboro AL 35768-0130
Albertville AL 35950-1775

Some city names are comprised of 2 words.
All states are 2-char abbreviations. Some zips
are 5-digit while others are ZIP+4. Any ideas???

MLH wrote:
[color=blue]
> Some city names are comprised of 2 words.
> All states are 2-char abbreviations. Some zips
> are 5-digit while others are ZIP+4. Any ideas???[/color]

Is this a one time thing? If so, I'd import the text into an excel
file, clean it up and then import into Access.

If it's a regular task you need to do, one of the first things I'd do is
contact whoever/whatever is producing this for you and ask for it as a
tab delimited text file or Excel or something.

If you must do it as you describe, regularly, ie, there's no way you can
get the source to comply with the above request, then I'd tell em at
work I have a headache and I'd go home and play video games (or in my
case develop Access apps for fun). Seriously, I could probably do it,
but I'd really need to sit and think about it.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

Am Wed, 07 Sep 2005 11:47:34 -0400 schrieb MLH:
[color=blue]
> I have a bunch of what you see below in a field
> named CSZ. I want to parse it the easiest way
> possible. I just don't know where to start...
>
> CSZ
> Anchorage AK 99518-3051
> Anchorage AK 99518-3051
> Juneau AK 99801-6917
> Palmer AK 99645
> Kenai AK 99611-1753
> Fairbanks AK 99709-5008
> Ketchikan AK 99901-1291
> Smiths Station AL 36877-3700
> Florence AL 35631-0419
> Gadsden AL 35901-3006
> Huntsville AL 35805-4106
> Scottsboro AL 35768-0130
> Albertville AL 35950-1775
>
> Some city names are comprised of 2 words.
> All states are 2-char abbreviations. Some zips
> are 5-digit while others are ZIP+4. Any ideas???[/color]

Hello,

if you couldn't get it as {tab} delimited field then
I would parse it form the right side.
maybe with the following code

lenghtoffield=len(csz)
strcsz=trim(csz)
'Search for the first blank from the right side of csz
'If the first blank is found then these rules apply
'2 characters to the left to n-1 is the state
'left from n-3 must be the city
for n=legthoffield to 0 step -1
if mid(strcsz,n,1)=" " then 'blank
strzip=mid(strcsz,n+1)
strcsz=left(strcsz,n-1)
exit for
endif
next
if n<=0 then 'test if there is a valid zip
msgbox "Not a valid string"
'Maybe you can do some errorhandling here
else
strstate=mid(strcsz,n-3)
strcity=left(strcsz,n-4)
endif

'Code for handling
'strzip = Zip from csz
'strstate = State from csz
'strcity = City from csz

Since you have todo this for every record this procedure isn't
very fast.
Also I didn't test it.

HTH
Karpi
<fluctuat nec mergitur>

Yeah, its a 1-time thing. I gotcha. I may just do it the hard way.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
[color=blue]
>
>Is this a one time thing? If so, I'd import the text into an excel
>file, clean it up and then import into Access.
>
>If it's a regular task you need to do, one of the first things I'd do is
>contact whoever/whatever is producing this for you and ask for it as a
>tab delimited text file or Excel or something.
>
>If you must do it as you describe, regularly, ie, there's no way you can
>get the source to comply with the above request, then I'd tell em at
>work I have a headache and I'd go home and play video games (or in my
>case develop Access apps for fun). Seriously, I could probably do it,
>but I'd really need to sit and think about it.[/color]

Thank-you very much, Hans. I will give this a try.
Very kind of you to offer such a comprehensive
solution to my problem. Let me know when I can
reciprocate.

Warmest regards,
Michael Harvell, eit
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

[color=blue]
>
>
>
>Hello,
>
>if you couldn't get it as {tab} delimited field then
>I would parse it form the right side.
>maybe with the following code
>
>lenghtoffield=len(csz)
>strcsz=trim(csz)
>'Search for the first blank from the right side of csz
>'If the first blank is found then these rules apply
>'2 characters to the left to n-1 is the state
>'left from n-3 must be the city
>for n=legthoffield to 0 step -1
> if mid(strcsz,n,1)=" " then 'blank
> strzip=mid(strcsz,n+1)
> strcsz=left(strcsz,n-1)
> exit for
> endif
>next
>if n<=0 then 'test if there is a valid zip
> msgbox "Not a valid string"
> 'Maybe you can do some errorhandling here
>else
> strstate=mid(strcsz,n-3)
> strcity=left(strcsz,n-4)
>endif
>
>'Code for handling
>'strzip = Zip from csz
>'strstate = State from csz
>'strcity = City from csz
>
>Since you have todo this for every record this procedure isn't
>very fast.
>Also I didn't test it.
>
>HTH
>Karpi
><fluctuat nec mergitur>
>[/color]

>I have a bunch of what you see below in a field[color=blue]
> named CSZ. I want to parse it the easiest way
> possible. I just don't know where to start...[/color]

I have an AutoFormat procedure I call from a number of data entry forms that
might give you some ideas - it's not perfect, but works for most stuff.

You'll need a comma:

Anchorage AK 99518-3051

needs to be:

Anchorage, AK 99518-3051

Another option would be to use RegEx - all the power you'll need...

Dim objRgx As Object
Set objRgx = CreateObject("VBScript.RegExp")
objRgx.Pattern = str1
objRgx.Test(str2)
...

Public Sub AutoFormat(frm As Form)
Dim i As Integer
Dim j As Variant
Dim m As Integer
Dim a As String
Dim c As String
Dim s As String
Dim z as String
Dim sC As String
Dim sS As String
Dim sZ As String
Select Case frm.Name
Case "frmAddressEdit"
sC = "City"
sS = "State"
sZ = "Zipcode"
a = frm.Controls(sC)
j = InStr(a, ",") 'comma required
If j Then
For i = j To Len(a)
n = Mid(a, i, 1)
If n Like "#" Then
m = i
Exit For
End If
Next i
c = Trim$(Left(a, j - 1))
s = Trim$(Mid(a, (j + 1), (m - j - 1)))
z = Trim$(Right(a, Len(a) - (m - 1)))
End If
If Len(c) Then frm.Controls(sC) = c
If Len(s) Then
If Len(s) = 2 Then s = UCase(s)
frm.Controls(sS) = s
End If
If Len(z) Then frm.Controls(sZ) = z
Case <other forms>
...

If you are still looking for a solution ----

Use the Instr function to find "spaceXXspace" where XX can be any two alpha
characters. Everything to the left of that string is City (doesn't matter
whether two words or one) and everthing to the right of that string is the
zip (doesn't matter whether 5 digit or 9 digit. The state is the two
caharacters between the spaces in that string.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
resource@pcdatasheet.com
www.pcdatasheet.com

"MLH" <CRCI@NorthState.net> wrote in message
news:io2uh1h3vmmolu8tstkh56a3eakv87noco@4ax.com...[color=blue]
>I have a bunch of what you see below in a field
> named CSZ. I want to parse it the easiest way
> possible. I just don't know where to start...
>
> CSZ
> Anchorage AK 99518-3051
> Anchorage AK 99518-3051
> Juneau AK 99801-6917
> Palmer AK 99645
> Kenai AK 99611-1753
> Fairbanks AK 99709-5008
> Ketchikan AK 99901-1291
> Smiths Station AL 36877-3700
> Florence AL 35631-0419
> Gadsden AL 35901-3006
> Huntsville AL 35805-4106
> Scottsboro AL 35768-0130
> Albertville AL 35950-1775
>
> Some city names are comprised of 2 words.
> All states are 2-char abbreviations. Some zips
> are 5-digit while others are ZIP+4. Any ideas???[/color]

Careful... that won't work with cities whose name contains two-letter words
(e.g., "City of Industry, CA" "Lake of the Woods, MN")

"PC Datasheet" <nospam@nospam.spam> wrote in message
news:UyJTe.8425$9i4.483@newsread2.news.atl.earthli nk.net...[color=blue]
> If you are still looking for a solution ----
>
> Use the Instr function to find "spaceXXspace" where XX can be any two
> alpha characters. Everything to the left of that string is City (doesn't
> matter whether two words or one) and everthing to the right of that string
> is the zip (doesn't matter whether 5 digit or 9 digit. The state is the
> two caharacters between the spaces in that string.
>
> --
> PC Datasheet
> Your Resource For Help With Access, Excel And Word Applications
> resource@pcdatasheet.com
> www.pcdatasheet.com
>
>
> "MLH" <CRCI@NorthState.net> wrote in message
> news:io2uh1h3vmmolu8tstkh56a3eakv87noco@4ax.com...[color=green]
>>I have a bunch of what you see below in a field
>> named CSZ. I want to parse it the easiest way
>> possible. I just don't know where to start...
>>
>> CSZ
>> Anchorage AK 99518-3051
>> Anchorage AK 99518-3051
>> Juneau AK 99801-6917
>> Palmer AK 99645
>> Kenai AK 99611-1753
>> Fairbanks AK 99709-5008
>> Ketchikan AK 99901-1291
>> Smiths Station AL 36877-3700
>> Florence AL 35631-0419
>> Gadsden AL 35901-3006
>> Huntsville AL 35805-4106
>> Scottsboro AL 35768-0130
>> Albertville AL 35950-1775
>>
>> Some city names are comprised of 2 words.
>> All states are 2-char abbreviations. Some zips
>> are 5-digit while others are ZIP+4. Any ideas???[/color]
>
>[/color]

With the configuration you've provided, all that's need is to know the
position of the last space in the string. In A2000 and later, you have
the InStrRev() function. In A97, there is no such thing, so I've
attached a roll-your-own version (xLastInStr()).

Table:
tblAddressParse
- strAddress (text)

Query:
SELECT
tblAddressParse.strAddress
, xlastinstr(RTrim([straddress])," ") AS break1
, [break1]-3 AS break2
, Left([strAddress],[break2]-1) AS city
, Mid([strAddress],[break2]+1,2) AS state
, Mid([strAddress],[Break1]+1) AS zip
FROM
tblAddressParse;

Function xLastInStr(ByVal tstr As String, twhat As String) As Integer
'*******************************************
'Purpose: Return location of last instance of a character or phrase.
'Inputs: Call xLastInStr("the quick brown fox jumped the lazy dog",
"the")
'Output: 28 - Location of last occurence of "the"
'*******************************************

Dim i As Integer
Dim n As Integer
Dim tlen As Integer

n = 0
tlen = Len(twhat)
For i = Len(RTrim(tstr)) To 1 Step -1
If Mid(tstr, i, tlen) = twhat Then
n = i
Exit For
End If
Next i

xLastInStr = n

End Function

Tested in A97 using your data.

HTH - Bob

Now that's a sweet little solution. I really like it.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxx

[color=blue]
>With the configuration you've provided, all that's need is to know the
>position of the last space in the string. In A2000 and later, you have
>the InStrRev() function. In A97, there is no such thing, so I've
>attached a roll-your-own version (xLastInStr()).
>
>Table:
> tblAddressParse
> - strAddress (text)
>
>Query:
>SELECT
> tblAddressParse.strAddress
> , xlastinstr(RTrim([straddress])," ") AS break1
> , [break1]-3 AS break2
> , Left([strAddress],[break2]-1) AS city
> , Mid([strAddress],[break2]+1,2) AS state
> , Mid([strAddress],[Break1]+1) AS zip
>FROM
> tblAddressParse;
>
>Function xLastInStr(ByVal tstr As String, twhat As String) As Integer
>'*******************************************
>'Purpose: Return location of last instance of a character or phrase.
>'Inputs: Call xLastInStr("the quick brown fox jumped the lazy dog",
>"the")
>'Output: 28 - Location of last occurence of "the"
>'*******************************************
>
>Dim i As Integer
>Dim n As Integer
>Dim tlen As Integer
>
> n = 0
> tlen = Len(twhat)
> For i = Len(RTrim(tstr)) To 1 Step -1
> If Mid(tstr, i, tlen) = twhat Then
> n = i
> Exit For
> End If
> Next i
>
> xLastInStr = n
>
>End Function
>
>Tested in A97 using your data.
>
>HTH - Bob[/color]

Exclude At, In, Of and To should take care of this.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
resource@pcdatasheet.com
www.pcdatasheet.com

"Bruce Rusk" <brusk_spambegone_@stanford.edoo> wrote in message
news:3cWdnasYh_ZIGYLeRVn-jQ@comcast.com...[color=blue]
> Careful... that won't work with cities whose name contains two-letter
> words (e.g., "City of Industry, CA" "Lake of the Woods, MN")
>
> "PC Datasheet" <nospam@nospam.spam> wrote in message
> news:UyJTe.8425$9i4.483@newsread2.news.atl.earthli nk.net...[color=green]
>> If you are still looking for a solution ----
>>
>> Use the Instr function to find "spaceXXspace" where XX can be any two
>> alpha characters. Everything to the left of that string is City (doesn't
>> matter whether two words or one) and everthing to the right of that
>> string is the zip (doesn't matter whether 5 digit or 9 digit. The state
>> is the two caharacters between the spaces in that string.
>>
>> --
>> PC Datasheet
>> Your Resource For Help With Access, Excel And Word Applications
>> resource@pcdatasheet.com
>> www.pcdatasheet.com
>>
>>
>> "MLH" <CRCI@NorthState.net> wrote in message
>> news:io2uh1h3vmmolu8tstkh56a3eakv87noco@4ax.com...[color=darkred]
>>>I have a bunch of what you see below in a field
>>> named CSZ. I want to parse it the easiest way
>>> possible. I just don't know where to start...
>>>
>>> CSZ
>>> Anchorage AK 99518-3051
>>> Anchorage AK 99518-3051
>>> Juneau AK 99801-6917
>>> Palmer AK 99645
>>> Kenai AK 99611-1753
>>> Fairbanks AK 99709-5008
>>> Ketchikan AK 99901-1291
>>> Smiths Station AL 36877-3700
>>> Florence AL 35631-0419
>>> Gadsden AL 35901-3006
>>> Huntsville AL 35805-4106
>>> Scottsboro AL 35768-0130
>>> Albertville AL 35950-1775
>>>
>>> Some city names are comprised of 2 words.
>>> All states are 2-char abbreviations. Some zips
>>> are 5-digit while others are ZIP+4. Any ideas???[/color]
>>
>>[/color]
>
>[/color]

Too risky -- there are also Spanish names in the Southwest that have
two-letter words in them (El Paso, Santa Fe, etc. .. can you be *sure*
you've got all of them?) ... Bob's Instrrev solution is safer and more
elegant.

"PC Datasheet" <nospam@nospam.spam> wrote in message
news:qb_Te.9400$FW1.38@newsread3.news.atl.earthlin k.net...[color=blue]
> Exclude At, In, Of and To should take care of this.
>
> --
> PC Datasheet
> Your Resource For Help With Access, Excel And Word Applications
> resource@pcdatasheet.com
> www.pcdatasheet.com
>
>
> "Bruce Rusk" <brusk_spambegone_@stanford.edoo> wrote in message
> news:3cWdnasYh_ZIGYLeRVn-jQ@comcast.com...[color=green]
>> Careful... that won't work with cities whose name contains two-letter
>> words (e.g., "City of Industry, CA" "Lake of the Woods, MN")
>>
>> "PC Datasheet" <nospam@nospam.spam> wrote in message
>> news:UyJTe.8425$9i4.483@newsread2.news.atl.earthli nk.net...[color=darkred]
>>> If you are still looking for a solution ----
>>>
>>> Use the Instr function to find "spaceXXspace" where XX can be any two
>>> alpha characters. Everything to the left of that string is City (doesn't
>>> matter whether two words or one) and everthing to the right of that
>>> string is the zip (doesn't matter whether 5 digit or 9 digit. The state
>>> is the two caharacters between the spaces in that string.
>>>
>>> --
>>> PC Datasheet
>>> Your Resource For Help With Access, Excel And Word Applications
>>> resource@pcdatasheet.com
>>> www.pcdatasheet.com
>>>
>>>
>>> "MLH" <CRCI@NorthState.net> wrote in message
>>> news:io2uh1h3vmmolu8tstkh56a3eakv87noco@4ax.com...
>>>>I have a bunch of what you see below in a field
>>>> named CSZ. I want to parse it the easiest way
>>>> possible. I just don't know where to start...
>>>>
>>>> CSZ
>>>> Anchorage AK 99518-3051
>>>> Anchorage AK 99518-3051
>>>> Juneau AK 99801-6917
>>>> Palmer AK 99645
>>>> Kenai AK 99611-1753
>>>> Fairbanks AK 99709-5008
>>>> Ketchikan AK 99901-1291
>>>> Smiths Station AL 36877-3700
>>>> Florence AL 35631-0419
>>>> Gadsden AL 35901-3006
>>>> Huntsville AL 35805-4106
>>>> Scottsboro AL 35768-0130
>>>> Albertville AL 35950-1775
>>>>
>>>> Some city names are comprised of 2 words.
>>>> All states are 2-char abbreviations. Some zips
>>>> are 5-digit while others are ZIP+4. Any ideas???
>>>
>>>[/color]
>>
>>[/color]
>
>[/color]

Gotta go along with you on this!

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
resource@pcdatasheet.com
www.pcdatasheet.com

"Bruce Rusk" <brusk_spambegone_@stanford.edoo> wrote in message
news:8vSdnbwumdnFEr3eRVn-uA@comcast.com...[color=blue]
> Too risky -- there are also Spanish names in the Southwest that have
> two-letter words in them (El Paso, Santa Fe, etc. .. can you be *sure*
> you've got all of them?) ... Bob's Instrrev solution is safer and more
> elegant.
>
>
> "PC Datasheet" <nospam@nospam.spam> wrote in message
> news:qb_Te.9400$FW1.38@newsread3.news.atl.earthlin k.net...[color=green]
>> Exclude At, In, Of and To should take care of this.
>>
>> --
>> PC Datasheet
>> Your Resource For Help With Access, Excel And Word Applications
>> resource@pcdatasheet.com
>> www.pcdatasheet.com
>>
>>
>> "Bruce Rusk" <brusk_spambegone_@stanford.edoo> wrote in message
>> news:3cWdnasYh_ZIGYLeRVn-jQ@comcast.com...[color=darkred]
>>> Careful... that won't work with cities whose name contains two-letter
>>> words (e.g., "City of Industry, CA" "Lake of the Woods, MN")
>>>
>>> "PC Datasheet" <nospam@nospam.spam> wrote in message
>>> news:UyJTe.8425$9i4.483@newsread2.news.atl.earthli nk.net...
>>>> If you are still looking for a solution ----
>>>>
>>>> Use the Instr function to find "spaceXXspace" where XX can be any two
>>>> alpha characters. Everything to the left of that string is City
>>>> (doesn't matter whether two words or one) and everthing to the right of
>>>> that string is the zip (doesn't matter whether 5 digit or 9 digit. The
>>>> state is the two caharacters between the spaces in that string.
>>>>
>>>> --
>>>> PC Datasheet
>>>> Your Resource For Help With Access, Excel And Word Applications
>>>> resource@pcdatasheet.com
>>>> www.pcdatasheet.com
>>>>
>>>>
>>>> "MLH" <CRCI@NorthState.net> wrote in message
>>>> news:io2uh1h3vmmolu8tstkh56a3eakv87noco@4ax.com...
>>>>>I have a bunch of what you see below in a field
>>>>> named CSZ. I want to parse it the easiest way
>>>>> possible. I just don't know where to start...
>>>>>
>>>>> CSZ
>>>>> Anchorage AK 99518-3051
>>>>> Anchorage AK 99518-3051
>>>>> Juneau AK 99801-6917
>>>>> Palmer AK 99645
>>>>> Kenai AK 99611-1753
>>>>> Fairbanks AK 99709-5008
>>>>> Ketchikan AK 99901-1291
>>>>> Smiths Station AL 36877-3700
>>>>> Florence AL 35631-0419
>>>>> Gadsden AL 35901-3006
>>>>> Huntsville AL 35805-4106
>>>>> Scottsboro AL 35768-0130
>>>>> Albertville AL 35950-1775
>>>>>
>>>>> Some city names are comprised of 2 words.
>>>>> All states are 2-char abbreviations. Some zips
>>>>> are 5-digit while others are ZIP+4. Any ideas???
>>>>
>>>>
>>>
>>>[/color]
>>
>>[/color]
>
>[/color]