I've imported an old dbase (DOS) mailing list database into FM5. The file imports fine, but I have one field called "citystate" inherited from the original containing, as you might imagine, both the city and the state. Is there an automated way to separate this data? The FM5 Help menu or printed documentation don't appear to readily address this issue.

To further clarify how this data appears in the "citystate" field, I think I should note that the "city..." is separated from the "...state" by a comma and space. An example would be: New York, NY. Again, is there a way to separate this into two fields? I'd like the first field to be "city" and the second to be "state". I could then add the comma and space in a layout if I need to make labels or something of that nature.

Thanks for your attention.

Create this script, run it once, then you can delete it:

Show All Records

Go to Record/Request/Page[First]

Loop

Set Field["city",Left(citystate,(Position(citystate,", ",1,1)-1))]

Set Field["state",Middle(citystate,(Position(citystate,", ",1,1)+2),2)]

Go To Record/Request/Page[Exit After Last, Next]

End Loop

Oh and double check the results to make sure none of the records have any wierd stuff in it.