Picking specific data from fields

[cr3ation]

This is a little odd. I'm trying to integrate an old mailing list into a new one, but the people who maintained this list didn't stick to a standard. I have the following fields for a contact:

"FirstName" - Betty

"LastName" - Smith

"FullName" (this is for mailing labels and if there's more than one person) - Betty Smith

"Salutation" (for form letters-this field automatically copies the first name) - Betty

"Organization" (for company names)

But the old list is as follows:

"LastName" (they also used this for company names) - Smith

"FullName" (they just threw in the first name here) - Betty Smith

"Salutation" (sometimes they used this...sometimes not)

I want pull the first name out of the "FullName" field and put it into a new "FirstName" field. How can I do that?

[bikergeek]

Calculation:

If (

PatternCount (

Trim ( FullName ) ;

" "

) = 1 ;

LeftWords ( FullName ; 1 ) ;

If (

PatternCount (

Trim ( FullName ) ;

Trim ( LastName )

) = 1 ;

Trim (

Sustitute ( FullName ; LastName ; "" )

) ;

"SpecialCase"

)

)

Checks for more than one space between words in FullName, or LastName not appearing in FullName, and in either case returns "SpecialCase" so you can search for that value and address those records individually. If LastName appears in FullName, the calc returns FullName minus LastName and extra spaces. If only one space appears in FullName between words, returns the first word (assumed to be the first name).

[cr3ation]

Thank you so much! It's going take a while to understand the mechanics of the script since I'm still trying to learn scripting. But it works like a charm especially since I'm dealing with 4000 records.

Thank you.