Parsing data

[Bunky]

I have looked at all the posts I could find but still have questions about
this.
I have a need to pull zipcode out of a 30 byte field. It could start in the
10 position or the 25th position. It is a length of five, except if it is an
international address, then who knows! Let's just stick with zipcode for
now. How do I go to the 30th position (the last blank) and keep going left
until I get a 5 digit zip?

Ideas?

 

[Jerry Whittle]

Something like this would extract a 5 character zip code that starts at the
25th position.

Zipcode25: Mid([TheString],25, 5)

However you mentioned a space. Is it the only space in the string? If so you
could use the InStr function to find the space then work from there.

Post what these strings look like. Include one at the 10th character, 25th
character, and maybe an international post code just for fun!

 

[Jeff Boyce]

I'm with Jerry on this ... "how" depends on "what", and you haven't shown us
what your data looks like...

More info (i.e., examples), please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[BELLA]

I have looked at all the posts I could find but still have questions about
this.
I have a need to pull zipcode out of a 30 byte field. It could start in
the
10 position or the 25th position. It is a length of five, except if it is
an
international address, then who knows! Let's just stick with zipcode for
now. How do I go to the 30th position (the last blank) and keep going
left
until I get a 5 digit zip?

Ideas?

 

[Larry Linson]

Seems to me, the OP was sufficiently explicit... for simplicity, limiting
the "ZIP" to a 5-bytes at the end of the actual data.

If the text field is stored in an Access table, my best recollection (and
confirmed for a field entered from a form in Access 2003) is that Access
does a RTrim to remove the trailing spaces, so no skipping over blanks back
to characters will be needed.

Thus (assuming the ZIP is the last thing in the field as seems to be the
case, and the field is named CSZ for City-State-ZIP), a "Left(CSZ,5)" would
retrieve a 5-digit ZIP (which to be sure should be checked to be numeric
characters)... or, if "Left(Left(CSZ,5),1) = "-", then you might suspect a
ZIP-plus-four.

You'd need to know the country and format of the country's Postal Code to
extend beyond US, territories, posessions, and military postal addresses,
though. If this is a "normally-formatted address line", then it may be worth
checking that the ZIP or ZIP-plus-four is preceded by a blank.

Larry Linson
Microsoft Office Access MVP

 

[Larry Linson]

Sorry, fingers working faster than brain. Below should read:

Right(CSZ,5) instead of Left(CSZ,5) and

Left(Right(CSZ,5),1) = "-" instead of Left(Left(CSZ,5),1) = "-"

Larry