How to Find duplicates in Name field?...Part 2

[Rocketman1]

My original May'06 Post#204069 states:

My data base has the entire name in one field, the "Name" field (data is imported from a data source), and there are multiple record for individuals. Sometimes the individuals name will include their Middile Initial, therefore FM does not see them a duplicates when executing a standard search for duplicates. For example:

Smith, John and Smith, John W. are not considered duplicates though they are the same person.

How can I get FM to see them as duplicates?

John Mark Osborne's response was: All you really want to do is remove the middle name if it exists. Or, you could transfer everything but the middle name to another field. Start by creating a new calculation field called Name2 with the following formula:

Case(Right(Name, 1) = ".", Left(Name, Length(Name) - 3), Name)

Now you will have a new field (Name2) with just the first and last name even if you have a last name like "Del Rio". You can search this new field for duplicates as long as the data entry is consistent.

It worked, but only on names with a period after the middle initial. I later discovered that others have a middle initial without a period, and some names with the middle name spelled in full. My need is to have only a Last Name (including two, or hypenated last names), a comma, and one First Name. I referenced FMPro 6 Help and read the available info on Case Function. I've tried several possible modifications to John coding, but am not successful thus far.

John, or anyone willing to help...Any suggestions? I would be most grateful for any help, or suggestions.

Thanks.

[Fitch]

This would tell you if the ending word was an initial:

Length( RightWords( Name ; 1 ) ) = 1

But that wouldn't solve the case where the middle name was spelled out.

What you want is the last name (the text up to the comma) and then the first word after the comma:

Let( [

comma = Position ( Name ; "," ; 1 ; 1 ) ;

nolast = Right ( Name ; Length ( Name ) - comma - 1 ) ];

Left ( Name ; comma ) & " " & LeftWords ( nolast ; 1 ) )