Breaking Up a Name

[Nip Lewis]

I tried several ways to update a Full Name such as

Abbott Wendy L into First Name Middle Initial Last Name to creat a mailing list.

I could get Last Name OK, but various iterations kept giving me LastName and Middle Initial only. Any suggestions??

Nip Lewis

[whanigsberg]

RE: Breaking Up a Name

First, there is a genie which will help you with this. I used it before I learned how to do such things from scratch. In fact, the genie taught me how to do it from scratch.

On the operations tab select new and then update and then from the character tab choose break a name into its parts.

Before I can help you to roll your own I need you to explain what you were trying to say when you wrote "various iterations kept giving me LastName and Middle Initial only".
What is the format of the list? Is it constant for all entries?

All in all, why not use the genie?

Bill

[Nip Lewis]

RE: Breaking Up a Name

Hi Bill

Thanks for the quick reply. I work with a voting list for the town and we have one field called Name: All data is the same as sample like this: Lewis Ralph M. I did use the Genie, etc. to try to follow the requirements to make the name break up into LastName, FirstName, MiddleInitial that I added to this database. I kept hetting the LastName Ok, but only the Middle initial unless some entries in database did not have a middle initial I got their firstname to come out in main database. I hope this is clear.

Nip Lewis

[whanigsberg]

RE: Breaking Up a Name

Nip,

I think I get it now. I would separate the data into three fields which you can then combine (or not)as necessary.

Last
First
Middle

You may have to do this in stages.
-put the first (left) word into lastname;
-put the second word into firstname;
-*if*there is a third word put it into middle.

Then you will be able to format however you want. I write little functions to do the formatting so you do the work once and use it on form, reports or wherever. One yields first middle last; one gives last, first middle.; etc.

It shouldn't be too difficult to strip out the individual words and test for the third. The only difficulty might come from people with two last names like my student from last term with the last name of Haddock Small. Following my advice above would have us calling her Small Haddock which wouldn't be good. Unless she had a hyphen between them which unfortunately I can't remember.

Seriously, is this an issue with your list and is it so big you won't be able to eyeball it for the few errors which will probably crop up? Perhaps you could run a query to catch all records with more than three "words" in the name field. You could then look at these more closely.

Bill

[Raymond Lyons]

RE: Breaking Up a Name

Where in the world do you live where the names are all as simple as "Lewis Ralph M"? You don't have a Mac Donald, a De La Garza, a Von Something, a Lewis Ralph M Jr or Sr or III, and so on and on?

The Genie does work on simple cases, but none of my cases are simple. I always seem to end up with a name field something like "Mr. Peter Du Charme Sr" and for these the genie is of little help.

But, you can write your own updates to strip out and rearrange various things. The sample below should give you some idea of how to proceed, though as usual someone on this board will have a "far better way" to do what I do. First, I always work on a copy of my original data so if I screw it all up I can go back to the original. In my case, the name in the single name field is frequently (but not always) is preceded by Mr or Ms, and where appropriate a suffix of Sr, Jr, III, etc. As I recall, the names may also have a middle initial but when they do I don't care about the initial, so if I remember correctly I strip it out. Thus in my case I might have MR PETER K WAYNE SR and all I want to end up with is First = PETER and Last = WAYNE. So I start with a Name field that has MR PETER K WAYNE SR and I end up with:
Name: PETER WAYNE
First: PETER
Last: WAYNE

But Name starts out MR FRANK DU CHARME JR, I end up with
Name FRANK DU CHARME
First: FRANK
Last: DU CHARME

As I said, I could put the middle initial in a separate field, but my application doesn't care about middle initials.

In any case, perusing the following update expressions should give you ideas for how to proceed with your own needs. All these updates are run at the same time by the way. You just have to make sure they are run in the order listed. You could also strip out suffixes like MD if you data had them (mine don't). Oh yeah, it has been a while since I looked at or ran the updates, so it's always possible there is a problem with them, though I don't think so or I would be posting them.

Raymond Lyons

********************

Name: IF(UT(WORD(NAME,1))="MR".OR. UT(WORD(NAME,1))="MS",WORD(NAME,2)+" "+WORD(NAME,3)+" "+WORD(NAME,4)+" "+WORD(NAME,5)+" "+WORD(NAME,6),NAME)

Name: IF(WORD(NAME,-1)="JR".OR.WORD(NAME,-1)="SR".OR.WORD(NAME,-1)="II".OR.WORD(NAME,-1)="IV",LEFT(NAME,LEN(TRIM(NAME))-2),IF(WORD(NAME,-1)="III",LEFT(NAME,LEN(TRIM(NAME))-3),NAME))

Now the first name is simple!

First: WORD(NAME,1)

It's the Last name that gets complicated (and would have to be adjusted for whatever name combinations are common in your area). And of course there will always be a few that need to be manually adjusted.

Last: IIF(W_COUNT(NAME)=2,WORD(NAME,2),IF(LEN(WORD(NAME,2))=1,ALLTRIM(SUBSTR(NAME,(LEN(WORD(NAME,1))+3),LEN(ALLTRIM(NAME)))),IF(LEN(WORD(NAME,2))=2.OR.WORD(NAME,2)="MAC".OR.WORD(NAME,2)="VAN".OR.WORD(NAME,2)="DEL".OR.WORD(NAME,2)="SAN".OR.WORD(NAME,2)="VON",ALLTRIM(SUBSTR(NAME,(LEN(WORD(NAME,1))+1),LEN(ALLTRIM(NAME)))),WORD(NAME,-1))))