Jump to content

City State Zip and plus 4 problem


This topic is 6536 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Thanks everyone for the "break name into three parts" help

I have a problem with the city state zip field.

I'm trying to parse data from one field to three fields

City State and Zip

The data looks like this:

Any ideas?

Here is what the data is like:

Lake Oswego, OR 97201

Portland, OR 97201-0000

Oregon CIty, OR 97830

Gresham, Oregon 97208-0001

West Linn, Oregon 97208-0001

Problem is Cities can be one or two words and Zip is one or two words.

Any Ideas?

Link to comment
Share on other sites

Hi Brian,

OK for the City...

Left( field, Position( field, "," , 1 , 1)-1)

For the zip...

RightWords( field, Case(Position( field, "-" , 1 , 1) = 0, 1, 2 ) )

In both these replace field with the field that contains all the text

Still looking at the state, will get back to you on that!

BTW, these calculations are dependnt on a comma aftr the City, and a hyphen if the zip is 2 words.

Regards

Ed

Link to comment
Share on other sites

It's all in the definition, I guess....

Here's a solution that worked for all the samples you listed....

CSZ = Data in current form... City, state, zip

City = Left( CSZ, Position( CSZ, "," , 1, 1)-1)

TruncState= Replace( CSZ, 1, Length(City)+2, "") This is an intermediate field.

State= If( Length(RightWords( TruncState, 1))=4, Replace( TruncState,

Length(TruncState)-10, 11, "") , Replace( TruncState, Length(TruncState)-5, 6, ""))

ZIP=If( Length(RightWords( CSZ, 1))=4, Right( CSZ, 10), Right( CSZ, 5))

My calculations were "interesting" when there was acidentally a carriage return at the end of the line in the CSZ field. frown.gif Makes counting from the end a litle more tricky.

Paul

Parsing City State Zip.pdf

Link to comment
Share on other sites

This topic is 6536 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.