Hi all,

I have been given a filemaker file that has only first and last name fields. In several hundred records the users put people's middle initials in the last or first name field, sometimes with a period and sometimes without. So they look like this:

FirstName: "Frank" LastName: "T. Doe"

FirstName: "Frank" LastName: "T Doe"

FirstName: "Frank T." LastName: "Doe"

FirstName: "Frank T" LastName: "Doe"

I am trying to put the middle initial into its own field and remove it from the other name fields, but I am not quite getting it.

Thanks in advance for any and all help!

See if this helps

http://help.filemaker.com/app/answers/detail/a_id/3638/~/extracting-title,-first,-middle,-and-last-name-from-a-single-field

Lee

Hi Valdrin,

This is a deep subject, as Lee's link indicates, but let's see how far we can get. We can discuss any possible tweaks after you review the calculation results. Actually, having the data already split into two fields makes it easier. Back up your file and then create these three calculations:

cFirstName ( text ):

Let ( [

words = WordCount ( FirstName ) ;

mid = Case ( words > 1 ; Length ( MiddleWords ( FirstName ; 2 ; 1 ) ) )

] ;

Case ( words =1 or words = 2 and mid = 1 ; LeftWords ( FirstName ; 1 ) ; LeftWords ( FirstName ; 2 ) )

)

cLastName ( text ) :

Let ( [

words = WordCount ( LastName ) ;

mid = Case ( words > 1 ; Length ( LeftWords ( LastName ; 1 ) ) )

] ;

Case ( words =1 or words = 2 and mid = 1 ; RightWords ( LastName ; 1 ) ; RightWords ( LastName ; 2 ) )

)

cMiddleName ( text ):

Let ( [

fullorig = WordCount ( FirstName & " " & LastName ) ;

fullnew = WordCount ( cFirstName & " " & cLastName ) ;

diff = fullorig - fullnew

] ;

If ( diff ;

Case (

WordCount ( FirstName ) = 1 ; LeftWords ( LastName ; 1 ) ;

WordCount ( LastName ) = 1 ; RightWords ( FirstName ; 1 ) ;

"ERROR"

))

)

Any ERROR in cMiddleName? Any other anomolies we should discuss? I am assuming this is a one-time import and that your data-entry Users are properly instructed as to the importance of keeping it that way. :^)

ADDED: The ERROR indicates that the calc identifies a middle name but cannot differentiate which to use. We can't also tell, on names such as Billie Ray or Bobbie Sue whether it is double first name or should be split. When ready, there are a few ways to update your data; I prefer using Define Fields rather than looping or replace...

Thanks, that worked great. I had figured out how to extract the first and last names, but was really having trouble getting the middle initials out. The error also found a bunch of other problems in the records -- things like a client's entire name placed in just one of the name fields.