Parsing Text

[Guest]

I want to divide text from one field into two separate ones. The text is in
the format "Tom and Susan" in the field MailName. I want it to be in
primaryFN "Tom" secondaryFN "Susan"

Essentially I want to cut the fields after the space after the word "and"

Any ideas?

 

[fredg]

I want to divide text from one field into two separate ones. The text is in
the format "Tom and Susan" in the field MailName. I want it to be in
primaryFN "Tom" secondaryFN "Susan"

Essentially I want to cut the fields after the space after the word "and"

Any ideas?

PrimaryFN = Left([MailName],InStr([MailName]," and ")-1)
SecondaryFN= Mid([MailName],InStr([MailName]," and ")+5)