City State Zip and plus 4 problem

[bkamlin]

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?

[EddyB]

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

[Damocles]

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. Makes counting from the end a litle more tricky.

Paul

Parsing City State Zip.pdf