Help stripping a zipcode

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to strip a 5 digit ZipCode from a string.

The string contains the City, State and either a 5 or 7 digit ZipCode.

Chicago IL 12345-7890
Chicago IL 12345

So the thought would be to identify the first blank space from the right and
then extract the 5 characters to the right of the blank.

Can someone help me get started?

Thanks

Dave
 
look up help on InStrRev (2000 SP2 and later) which can be used to find
the first space starting from the right. Another method would be to use
Instr and search for the dash and combine that with the MID function


Mid([CityStateZip],Instr(1,[CityStateZip] & "-","-") -6,5)



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
Thank you!!

That did it :-)

Dave

Ofer Cohen said:
Try

Mid([FieldName],InStrRev( [FieldName]," ")+1,5)

--
Good Luck
BS"D


Dave said:
I want to strip a 5 digit ZipCode from a string.

The string contains the City, State and either a 5 or 7 digit ZipCode.

Chicago IL 12345-7890
Chicago IL 12345

So the thought would be to identify the first blank space from the right and
then extract the 5 characters to the right of the blank.

Can someone help me get started?

Thanks

Dave
Click to expand...
Click to expand...
 
I want to strip a 5 digit ZipCode from a string.

The string contains the City, State and either a 5 or 7 digit ZipCode.

Chicago IL 12345-7890
Chicago IL 12345

So the thought would be to identify the first blank space from the right and
then extract the 5 characters to the right of the blank.
Click to expand...

Well, that falls down when you get to San Francisco or Salt Lake City.

You can (in A2002 or later) use the InStrRev function - In String Reverse:

Left(Mid(InStrRev([CityStateZip], " ")+1, 5)

InStrRev will find the position of the *last* blank in the field; Mid() will
extract whatever is to the right of that (e.g. 12345 or 12345-4122), and
Left() will get just the five digits.

John W. Vinson [MVP]
 
Back
Top