Trim state and zip code from city

[imageron]

I'm a newbie. My previous data base had city state and zip in one field. I imported into FMPro7 and would like to find a way to populate 2 new fields with this info so I can search and sort. I would like to leave the city and remove the state and zip in the original field. Thanks, any ideas would be appreciated.

[MarkWilson]

Make a backup before trying this solution!

Use a looping script and the three formulae below.

If you do want to keep the city field as is change all references to Address::Address_t to Address::City_t

To isolate the zip code:

Set Field [Address::Zip_Code_t; Filter( Address::Address_t ; "0123456789" )]

To isolate the state:

Set Field [Address::Stae_t; RightWords( Substitute( Address::Address_t ; Address::Zip_Code_t ; "" ) ; 1 )]

To isolate the city:

Set Field [Address::City_t; Trim( Substitute( Substitute( Filter( Address::Address_t ; "abcdefghijklmnopqrstuvwxyz ABCDEFGHIJKLMNOPQRSTUVWXYZ" ) ; Address::Zip_Code_t ; "" ) ; Address::State_t ; "" ) )]

[comment]

That doesn't seem right. First, the filter would ruin some fine cities names, e.g. "Batesburg-Leesville" or "Ciudad Juárez". Substituting out the state could be even worse, e.g. "NEBRASKA CITY, NE" would return "BRASKA CITY", and "New York, New York" would return an empty string.

I think we need to know more about the format of the imported data (an example would be useful). If it's a standard "City Name 00000 ST", then a calculation field (result is Text) =

LeftWords ( Imported ; WordCount ( Imported ) - 2 )

should do the job. Once you are satisfied with the result, you can change the calculation field to a regular text field (provided the calculation is stored) and delete the imported field.

[imageron]

Data was exported into an Excel tab-delimited file which was imported into FM. Original "citystatezip" field has all 3 elements: various city names (1,2,or 3 name), space, 2 letter state abbreviation, space, and a trailing 5 numerical zip.I would like to strip last 2 elements and populate new respecive fields, leaving original field with just city name.

[comment]

Have you tried the calculation I suggested?

[Raybaudi]

City:

LeftWords ( citystatezip ; WordCount ( citystatezip ) - 2 )

------------

Zip:

RightWords ( citystatezip ; 1 )

------------

State:

Trim ( Substitute ( citystatezip ; [ City ; "" ] ; [ Zip ; "" ] ) )

------------

[comment]

Why not stick with the same method and let State =

MiddleWords ( citystatezip ; WordCount ( citystatezip ) - 1 ; 1 )

I know it's not likely to have a city named AL in Alabama, but still...

[imageron]

I'm not exactly clear how to do it. I haven't studied the scripts in detail yet, but if you could help me get going by giving me advise that would help me, that would be great. I know there are standard scripts, with canned parameters and actions, but I imagine what you have written is custom. Not sure where to go with that.

[comment]

There's no scripting required here. Define three calculation fields, City, Zip and State (result is Text) with the respective formulae above (use your own imported field name instead the names we have made up).

Put the fields on the layout and eye-ball the results. If you're happy with it, change the fields' type from Calculation to Text. Check the fields again, and if everything is OK, you can delete the imported field.

[imageron]

OK I'm on a roll Using rayboudi's suggestion, but substituting

State = MiddleWords ( citystatezip ; WordCount ( citystatezip ) - 1 ; 1 )

I got it to work. I had to move order of Zip field ahead of state. Still have a minor problem: Original field still has original combined data. Would like to have only City name populating original field. If that's a problem, I guess could create a new city field, which I would need a formula for. Thanks for all your help so far.

[imageron]

OK I just created a new city field with the following formula:

LeftWords ( AddrCityStateZip ; WordCount (AddrCityStateZip ) -2 ) ; 1 )

Only the city name appears (also names with 2 or 3 words). So problem solved! Thanks again. Not exactly sure what I'm doing but it worked.

Another related task: moving all the data from the wrong field to the correct one in some records. ( i.e. in my previous data base, if there was no contact name, address appeared in contact field, City State zip appeared in Street field.