Reordering Text using a calculation

[dfiller]

Two questions. I'm not very good with FileMaker calculations...

If I'm givin a tab delimited file containing a field that has someones name as - Doe, John - How do I use a calculation to take that field and and reorder the users names to John, Doe? Also, how would I go about taking that field, and seperating both the last and first names into their own two fields? Not sure what functions to use or how to go about this.

Thanks in advance.

~Daniel

[BobWeaver]

As long as the field always has the comma in it, it's easy:

LastName = Left(InputField;Position(InputField;",";1;1)-1)

FirstName = Middle(InputField; Position(InputField;",";1;1)+1;9999)

So:

FullName = FirstName & " " & LastName

[Vaughan]

I'd approach this job by creating a small FMP database to act as an import processing file: the tab-delimited data gets imported into the FMP database and get turned into records and fields. Re-ordering the rows then becomes a matter of sorting the records.

Splitting names is always a problem, because not all entries follow the "firstname lastname" format where the first and last names are one word each.

How would you split "Mary Anne Smith" and "Vince De Saul"?

If the name field has only two words then it's easy. But if there are more than two words human intevention will probably be necessary. We can still make a guess and say that the last word is the surname: all the rest is the first name.

LastName = RightWords( namefield ; 1 )

FirstName = LeftWords( namefield ; WordCount( namefield ) - 1 )

I'd make a calc field that counted all the words in the name field, then find all those records with more than 2 words and process them by hand.

[dfiller]

Thanks for your advice, will your functions work if the field involves a comma? I'll be sure to try this out tomorrow.

[dfiller]

Thanks for all your help. That worked perfectly.