Separating Fields

Greetings,

We are redoing our non-profit organization's database and I had a question. Right now, the database for our organization has essentially two fields for each person: name and address. For instance our first field would say "Jack Samuelson" and our Second field would have the full address:"30 Elm Street. Springfield, New Jersey. 62838."

Now my question is... is there any way, either through macro or formula, that we can separate these field for instance. I.e. is it possible to turn this:

Field 1: Jack Samuleson
Field 2: 30 Elm Street. Springfield, New Jersey. 62838.

into this:

Field 1: Jack
Field 2: Samuelson
Field 3: 30 Elm Street
Field 4: Springfield
Field 5: New Jersey
Field 6: 62838

Any help would be greatly appreciated. It would take ages to change everything in manually. If we can do it all at once, it would save us a lot of time and money.

As long as there are no middle initials or names the first two should work with something like this

Field 1 - =LEFT(A2,FIND(" ",A2)-1)
Field 2 - =MID(A2,FIND(" ",A2)+1,LEN(A2))

Gets a little for confusing here

Field 3 - =LEFT(A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1)) assuming all are three words

Field 4 - =MID(A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1)+1,(FIND(",",A1)-1)-D1) assuming there is a coma after the city and none before that

Field 5 - =MID(A1,FIND(",",A1)+2,LEN(A1)-FIND(",",A1)-6) assuming all 5 digit zips and comma thing from above

Field 6 - =RIGHT(A1,5) assuming all 5 digit zips

I was assuming that the name was in cell a2 and the address was in cell a1.

Not sure it will fit every posibility but it may take care of a large number of them for you.

Hope this helps some.