I have a small problem that I'm sure someone has solved before, so if this has already been discussed, feel free to point to the link:

I have a database with first and last name fields into which I often have to import data with the full name as a single field, so I have a "split first-last name" script that does this (full name is in the first name field at start of script):

Replace (lastname, RightWords ( firstname ; 1 ))

Replace (firstname, LeftWords ( firstname ; WordCount ( firstname ) - 1 )

The problem is that if there is a middle initial, the period after the initial disappears, so "John B. Doe" becomes "John B Doe". How can I hang on to those periods?

If you are sure that the last word - and only the last word - is the last name, you could try something like:

Trim ( Left ( text ; Length ( text ) - Length ( RightWords ( text ; 1 ) ) ) )

There isn't any sure way using the Left, Middle, and Right to split names. The reason is the inconsistency in which User will enter the data in a one field name.

The best solution to cover most of the different formats, is to have the users enter the data into separate fields to begin with. Unfortunately, we sometimes don't have control over imports, so it becomes a matter of either separating the data in a text editor, or creating calculations that can do it for us.

Using the RightWords can fail for names such as van Dyke, or Smith Jr., etc.

Take a look at the File here Posted by Fenton

HTH

Lee

comment's suggestion works for me. It's true that the last word is not always the last name, but that's a separate issue. I usually have to go through and fix a few things anyway, but not having to replace periods after initials will eliminate a lot of the time I spend on those kinds of fixes.

And the NameParse file Lee suggested looks promising, too.

Thanks a lot!

Edited April 8, 200817 yr by Guest