ClarisWorks: Separating City, State and ZIP into Separate Fields

This information was provided by Claris Corporation on 16 March 1998, and incorporated into Apple Computer's Tech Info Library.
In many databases it is common to enter city, state and zip information into one category (field). However, if you separate this information into separate fields, you'll have more flexibility in formatting this information on a layout, and in finding and sorting records. When you convert a database file from your old database program to ClarisWorks, the city, state and zip information may still be in a single field. Here is a suggested way to separate this information into three fields:

Note 1: This example assumes that you have entered the city, state and zip information with a coma after the city, and the two letter designation for the state. This example also assumes that the field containing the city, state and zip information is named "CSZ".

City (calculation,text) = IF(FIND(",",'CSZ',1),LEFT('CSZ',FIND(",",'CSZ',1)-1),LEFT('CSZ',FIND(" ",'CSZ',1)-1))

State (calculation,text) = MID('CSZ', FIND(",", 'CSZ',1) +2 , 2)

Zip 9 (calculation,text) = IF (FIND ("-",'CSZ',1),RIGHT 'CSZ',10),RIGHT ('CSZ',5))

Note 2: Make sure your "format results as" selection is set to Text. If not, click and drag to change it. Check the three fields in Browse mode to verify that they correctly separated the information. If so, return to Define Fields and change each field from a Calculation field to a Text field. Finally delete the original Text field.
Published Date: Feb 18, 2012