Splitting single field into 3 fields

[Pete Provencher]

Using Access 2003:

Fuquay Varina NC 27526
Raleigh NC 27611-5458
Zebulon NC 27597

I need to split the above into 3 fields city, State, Zip

I tried starting with zip and tried the following formula:
Right([addr],Len([addr])-InStr(1,[addr]," ")-1) which does not work as it
returns diffeent lengths.

If anyone knows a way to get this done you help would be greatly
appreciated.

Pete Provencher

 

[JimS]

Try searching for the first instance of a digit (0-9) following a blank.
That'll give you the location of the beginning of the zip, and by default,
the length of the zip.

This won't work for Canadian zip codes or European Zip codes that aren't
numeric.

 

[BruceM]

Assuming that the format (spaces between state and zip, etc.) is consistent,
you could do something like this in a query to extract the zip code:
ZipPart: IIf(Left(Right([addr],5),1)="-",Right([addr],10),Right([addr],5))

And something like this to extract the state:
StatePart:
IIf(Left(Right([addr],5),1)="-",Right(Left([addr],Len([addr])-11),2),Right(Left([addr],Len([addr])-6),2))

And then to get the city:
CityPart: IIf(Left(Right([addr],5),1)= "-",Left([addr],Len([addr]) -
14),Left([addr],Len([addr]) - 9))

I have shown a way to get the results in calcualted query fields, but you
could also do this in an update query, with the expressions (minus ZipPart:,
etc.) as the Update To value in new fields.

 

[JimS]

Sorry, I guess I could have been more helpful.
dim i as integer
dim blah(10) as string
blah=split(addr)
for i = 0 to ubound(blah)
if isnumeric(left(blah(i),1)) then
you found it
endif
next i

Don't forget to do something if there is no zip.

 

[Pete Provencher]

Your formulas worked like a charm. THanks so very much. You're a life saver.
Now I have to see if I can understand exactly what they are doing.

Pete Provencher

Assuming that the format (spaces between state and zip, etc.) is
consistent, you could do something like this in a query to extract the zip
code:
ZipPart: IIf(Left(Right([addr],5),1)="-",Right([addr],10),Right([addr],5))

And something like this to extract the state:
StatePart:
IIf(Left(Right([addr],5),1)="-",Right(Left([addr],Len([addr])-11),2),Right(Left([addr],Len([addr])-6),2))

And then to get the city:
CityPart: IIf(Left(Right([addr],5),1)= "-",Left([addr],Len([addr]) -
14),Left([addr],Len([addr]) - 9))

I have shown a way to get the results in calcualted query fields, but you
could also do this in an update query, with the expressions (minus
ZipPart:, etc.) as the Update To value in new fields.

Using Access 2003:

Fuquay Varina NC 27526
Raleigh NC 27611-5458
Zebulon NC 27597

I need to split the above into 3 fields city, State, Zip

I tried starting with zip and tried the following formula:
Right([addr],Len([addr])-InStr(1,[addr]," ")-1) which does not work as it
returns diffeent lengths.

If anyone knows a way to get this done you help would be greatly
appreciated.

Pete Provencher

 

[BruceM]

Your formulas worked like a charm. THanks so very much. You're a life
saver. Now I have to see if I can understand exactly what they are doing.

Pete Provencher

Assuming that the format (spaces between state and zip, etc.) is
consistent, you could do something like this in a query to extract the
zip code:
ZipPart:
IIf(Left(Right([addr],5),1)="-",Right([addr],10),Right([addr],5))

Break this one down to see what is happening. Right([addr],5) is the
rightmost five characters in the [addr] field. In two of your addresses
that is the five-digit zip code. In the other it is the dash plus the four
numbers following it. In other words: If it is a nine-digit zip code, do
one thing, otherwise do something else. Let's say it is -5458 (from
Raleigh). Now look at the Left function:
Left("-5458",1) is the dash; in the other two zip codes it is a number. If
it is a dash (that is, if the first part of the IIf statement (before the
first comma) is true, the formula after the first comma applies: the
rightmost 10 characters in the field are "27611-5458". If the first
expression is false (firth character from the right is not a dash), then the
formula grabs the rightsmost 5 characters.

And something like this to extract the state:
StatePart:
IIf(Left(Right([addr],5),1)="-",Right(Left([addr],Len([addr])-11),2),Right(Left([addr],Len([addr])-6),2))

Similar principles apply here. The first part is the same as the zip code
expression. Look at the "innermost" expression after the first comma:
Len([addr])-11. This is subtracting 11 from the length of the field (nine
digits, a dash, and the space before the zip code). In the case of Raleigh,
that leave 10 characters. So:
Left([addr],10) = "Raleigh NC"
Right("Raleigh NC",2) is the rightmost 2 characters from the text string.
With a five-digit zip code the expression after the second comma applies, so
the string is shortened by 6 characters (zip code and one space) to leave
the city plus the state abbreviation.

The next expression just adds three to the number subtracted from the length
of the string (i.e. the number of characters in the field), which leaves
just the city.

You were fortunate that the format was regular. This sort of thing can get
pretty messy.

And then to get the city:
CityPart: IIf(Left(Right([addr],5),1)= "-",Left([addr],Len([addr]) -
14),Left([addr],Len([addr]) - 9))

I have shown a way to get the results in calcualted query fields, but you
could also do this in an update query, with the expressions (minus
ZipPart:, etc.) as the Update To value in new fields.

Using Access 2003:

Fuquay Varina NC 27526
Raleigh NC 27611-5458
Zebulon NC 27597

I need to split the above into 3 fields city, State, Zip

I tried starting with zip and tried the following formula:
Right([addr],Len([addr])-InStr(1,[addr]," ")-1) which does not work as
it returns diffeent lengths.

If anyone knows a way to get this done you help would be greatly
appreciated.

Pete Provencher

 

[Pete Provencher]

Thanks for your explanation. It is really helpfull.

Pete Provencher

Your formulas worked like a charm. THanks so very much. You're a life
saver. Now I have to see if I can understand exactly what they are doing.

Pete Provencher

Assuming that the format (spaces between state and zip, etc.) is
consistent, you could do something like this in a query to extract the
zip code:
ZipPart:
IIf(Left(Right([addr],5),1)="-",Right([addr],10),Right([addr],5))

Break this one down to see what is happening. Right([addr],5) is the
rightmost five characters in the [addr] field. In two of your addresses
that is the five-digit zip code. In the other it is the dash plus the
four numbers following it. In other words: If it is a nine-digit zip
code, do one thing, otherwise do something else. Let's say it is -5458
(from Raleigh). Now look at the Left function:
Left("-5458",1) is the dash; in the other two zip codes it is a number.
If it is a dash (that is, if the first part of the IIf statement (before
the first comma) is true, the formula after the first comma applies: the
rightmost 10 characters in the field are "27611-5458". If the first
expression is false (firth character from the right is not a dash), then
the formula grabs the rightsmost 5 characters.

And something like this to extract the state:
StatePart:
IIf(Left(Right([addr],5),1)="-",Right(Left([addr],Len([addr])-11),2),Right(Left([addr],Len([addr])-6),2))

Similar principles apply here. The first part is the same as the zip code
expression. Look at the "innermost" expression after the first comma:
Len([addr])-11. This is subtracting 11 from the length of the field (nine
digits, a dash, and the space before the zip code). In the case of
Raleigh, that leave 10 characters. So:
Left([addr],10) = "Raleigh NC"
Right("Raleigh NC",2) is the rightmost 2 characters from the text string.
With a five-digit zip code the expression after the second comma applies,
so the string is shortened by 6 characters (zip code and one space) to
leave the city plus the state abbreviation.

The next expression just adds three to the number subtracted from the
length of the string (i.e. the number of characters in the field), which
leaves just the city.

You were fortunate that the format was regular. This sort of thing can
get pretty messy.

And then to get the city:
CityPart: IIf(Left(Right([addr],5),1)= "-",Left([addr],Len([addr]) -
14),Left([addr],Len([addr]) - 9))

I have shown a way to get the results in calcualted query fields, but
you could also do this in an update query, with the expressions (minus
ZipPart:, etc.) as the Update To value in new fields.

Using Access 2003:

Fuquay Varina NC 27526
Raleigh NC 27611-5458
Zebulon NC 27597

I need to split the above into 3 fields city, State, Zip

I tried starting with zip and tried the following formula:
Right([addr],Len([addr])-InStr(1,[addr]," ")-1) which does not work as
it returns diffeent lengths.

If anyone knows a way to get this done you help would be greatly
appreciated.

Pete Provencher

 

[BruceM]

Glad to help. I do need to say that the phrase "In other words: If it is a
nine-digit zip code, do one thing, otherwise do something else" is in the
wrong place in the explanation. What I intended to say in due course is
that the first part of the IIf expression:
IIf(Left(Right([addr],5),1)="-",
evaluates to True if the zip code is 9-digit (specifically, it is True if
the fifth character from the right is a dash); otherwise it evaluates to
False.
Also, I noticed that I used the word "firth", which is intended to be
"fifth" (not "fourth", which is another possible interpretation of the
misspelling).

Thanks for your explanation. It is really helpfull.

Pete Provencher

Your formulas worked like a charm. THanks so very much. You're a life
saver. Now I have to see if I can understand exactly what they are
doing.

Pete Provencher
Assuming that the format (spaces between state and zip, etc.) is
consistent, you could do something like this in a query to extract the
zip code:
ZipPart:
IIf(Left(Right([addr],5),1)="-",Right([addr],10),Right([addr],5))

Break this one down to see what is happening. Right([addr],5) is the
rightmost five characters in the [addr] field. In two of your addresses
that is the five-digit zip code. In the other it is the dash plus the
four numbers following it. In other words: If it is a nine-digit zip
code, do one thing, otherwise do something else. Let's say it is -5458
(from Raleigh). Now look at the Left function:
Left("-5458",1) is the dash; in the other two zip codes it is a number.
If it is a dash (that is, if the first part of the IIf statement (before
the first comma) is true, the formula after the first comma applies: the
rightmost 10 characters in the field are "27611-5458". If the first
expression is false (firth character from the right is not a dash), then
the formula grabs the rightsmost 5 characters.

And something like this to extract the state:
StatePart:
IIf(Left(Right([addr],5),1)="-",Right(Left([addr],Len([addr])-11),2),Right(Left([addr],Len([addr])-6),2))

Similar principles apply here. The first part is the same as the zip
code expression. Look at the "innermost" expression after the first
comma: Len([addr])-11. This is subtracting 11 from the length of the
field (nine digits, a dash, and the space before the zip code). In the
case of Raleigh, that leave 10 characters. So:
Left([addr],10) = "Raleigh NC"
Right("Raleigh NC",2) is the rightmost 2 characters from the text string.
With a five-digit zip code the expression after the second comma applies,
so the string is shortened by 6 characters (zip code and one space) to
leave the city plus the state abbreviation.

The next expression just adds three to the number subtracted from the
length of the string (i.e. the number of characters in the field), which
leaves just the city.

You were fortunate that the format was regular. This sort of thing can
get pretty messy.

And then to get the city:
CityPart: IIf(Left(Right([addr],5),1)= "-",Left([addr],Len([addr]) -
14),Left([addr],Len([addr]) - 9))

I have shown a way to get the results in calcualted query fields, but
you could also do this in an update query, with the expressions (minus
ZipPart:, etc.) as the Update To value in new fields.

Using Access 2003:

Fuquay Varina NC 27526
Raleigh NC 27611-5458
Zebulon NC 27597

I need to split the above into 3 fields city, State, Zip

I tried starting with zip and tried the following formula:
Right([addr],Len([addr])-InStr(1,[addr]," ")-1) which does not work as
it returns diffeent lengths.

If anyone knows a way to get this done you help would be greatly
appreciated.

Pete Provencher