Reformat name field

[rcorbitt]

I am importing a text file that contains (Name) "Smith, Fred A." or "Doe, John Robert" or "Doe, John Robert Sr." or "Jones, Sam".

I want to create a calc that will reformat the data to "Fred A. Smith" or "John Robert Doe" or "John Robery Doe Sr." or "Sam Jones".

I tried this:

RightWords (Name; 2 ) & " " & LeftWords (Name; 1)

It works ok when there is a middle initial, but fails miserably with only a first/last or a first/last/suffix.

How do I write a calc that uses the comma as the delmiter for the last name, and spaces for the first and middle and suffix?

Thanks in advance.

[comment]

It's fairly easy to separate the last name from the rest using the position of the comma as the anchor:

last = Left ( Name ; Position ( Name ; "," ; 1 ; 1 ) - 1 ) ;

rest = Right ( Name ; Length ( Name ) - Position ( Name ; "," ; 1 ; 1 ) - 1 )

Hovewer, a computer cannot tell by itself if "Sr." is a suffix or an initial. You might want to look at the "Universal Capitalizer" demo here to get some ideas how it can be handled, but it can never be perfect.

[rcorbitt]

THANKS!

This works pretty well but ...

The fourth "word" of the field will always be the suffix, if it exists; and I need to move it to the end. (BTW - Universal Capitalizer is incredible, I have it working on the address field already.)

Currently "Doe, John Robert Sr." becomes "John Robert Sr. Doe".

How do I get the fourth word to the end, only if it exists? "John Robert Doe Sr."

[comment]

The fourth "word" of the field will always be the suffix

Will it? How about "Jones, Sam Jr." or "Smith-Jones, John Irving Sr."? You could of course make an assumption that if the 'rest' part has three words, the third word is a suffix. But it wouldn't catch those who don't have an initial and do have a suffix, and it would catch - falsely - those with three first names.

[rcorbitt]

Maybe a better solution would be just first and last name.

Suggestions?

[comment]

I don't think there's much to add. The only thing I would suggest is to separate the data into individual fields (FirstName, LastName, Initial, Suffix, Salutation, etc.). It's very easy to put data together, not so easy to take it apart.

As I mentioned before, you could make the suffix the last word of "rest" when "rest" has more than 2 words, and mirror the first name calculation accordingly. But I believe in the end there's no alternative to a human eyeballing the results and making intelligent decisions.