Separating Address Information

[ruadork]

I am working on a database that takes data from a student info database (Apple's PowerSchool) and uses it in FileMaker for busing information.

My problem is PowerSchool lists a student's address as "123 Street Rd" (one field), but I need each piece of the address in FileMaker to be in separate fields so it can be sorted by street, etc.

I can write a calculation to separate "123 Street Rd" into "123" and "Street Rd," but sometimes there will be a N, S, E, or W in front of the road, or there will be a POBox after the entire street address, or there will be an apartment or floor in the address. Each of these needs to be a separate field.

Has anyone dealt with addresses like this where the given data is unpredictable, but I somehow need to separate into usable fields? If so, do I do this by calc fields?

At the moment, there are about 10,000 student addresses in the PowerSchool database, so editing all student addresses to get them into the same format is not an option.

Thanks for your help.

Andrew

[Riley Waugh]

You might want to look at "custom Parsing" at http://www.nightwing.com.au/FileMaker/demos5.html