How to Parse First & Last Name

[wingwalker]

We need to import data from an Excel document that has First and Last Name and some with middle initial, in the same field. Each name is on a separate line, but as I said, "John Doe" or "John B. Doe" is in one field. My FileMaker solution has two separate fields of "FirstName" and "LastName". How do I get the names in their respective fields?

Thanks for your help,

James

[mr_vodka]

A very quick but crude way of doing this is to import your data into a FM table (imported_data),

Then run a loop script that will parse out the field FullName into the table with the fields, LastName and FirstName.

You can do a simple loop like:

If [WordCount (RawData::FullName) > 2 ]

   Set Field [Parsed::FirstName; LeftWords (RawData::FullName); 2)]

   Set Field [Parsed::LastName; RightWords (RawData::FullName); 1)]

ElseIf [WordCount (RawData::FullName) =2]

   Set Field [Parsed::FirstName; LeftWords (RawData::FullName); 1)]

   Set Field [Parsed::LastName; RightWords (RawData::FullName); 1)]

Else

   Set Field [Parsed::gErrorLog; gErrorLog & ¶ & RawData::RecordID ]

EndIf

Go to Record/Request/Page [Next; Exit after last]

End Loop

Where gErrorLog is a global field to capture any Record numbers where the raw data might have less than 2 words.

Good luck.