breaking up an address line into other fields ?

[hartmut77]

Hi

I am having a problem breaking up and address field that was pasted into filemaker. I want the field to be broken up into city , state and zip but here is the way the data is:

Oneida, NY 13421

Massapequa, NY 11758-6026

New York, NY 10019-1022

New Hyde Park, NY, 10001

So I can't do it by middle words, , I can't do the zips by right words, because sometimes the zip is zip +4 which constitutes another word.

How can I consistently break up the address line into the three fields with a script?

Thanks in Advance

Dave

[Pupiweb]

IF the data always has the structure you describe yopu might retrieve the city with

Left (Address, Position (Address,",",1,1)

retrieve the State getting the 3 characters to the right of the comma and trimming the leading space

Trim (

Middle (Address, Position (Address,",",1,1),3))

and the ZIP code by getting all characters to the right of the second space after the first comma

Middle (Address, Position (Address," ",Position (Address,",",1,1),2),9999))

I haven't tested the above formulas so they probably need to be tweaked somehow

[Munchie]

I've been looking for a way to do something very similar...

I have social security numbers stored in fields called "Notes" The only thing consistant about it is that all the SSN's are patterend ###-##-#### Is there any way to find this pattern, remove the dashes, and put it into a field?

Also, could somebody explain exacly how to use the Position function?

Thanks!

[hartmut77]

Thanks That worked great

I really appreciate your help

Dave