# Thread: LastName, Firstname

1. ## LastName, Firstname

I just created this code to convert a Firstname Lastname into a Lastname, Firstname:

substr(alltrim(firstlast),(len(alltrim(Firstlast))-rat(" ",alltrim(Firstlast),1)+2),50)+", "+substr(Firstlast,1,at(" ",alltrim(Firstlast),1)-1)

...where Firstlast is the name of field containing the Firstname Lastname.

What I can't figure out is how to handle "Jr's", middle initials, etc.

Anyone have a good solution?

Charlie

2. ## RE: LastName, Firstname

Charles:
A much simpler approach:
word(fullname,2)+" "+word(fullname,1)+" "+word(fullname,3)

dim fullname as c
fullname="charles Hoens Jr."
?word(fullname,2)+" "+word(fullname,1)+" "+word(fullname,3)
= "Hoens charles Jr"

fullname="Charles Hoens"
?word(fullname,2)+" "+word(fullname,1)+" "+word(fullname,3)
= "Hoens Charles "

Gabe

3. ## RE: LastName, Firstname

Obviously you have a structure problem. It might be possible to construct an expression that allowed for all the possibilities, but as the other response indicates, you would have to go to great lengths reassemble the name parts considering that you might have

firstname lastname
firstname midinitial lastname
firstname middlename lastname
firstname lastname sufffix
firstname midinitial lastname suffix
firstname middlename lastname suffix

and likely other combinations.

You need fields for each of the parts, requiring only those you have to have, and assemble the parts in whatever order you want at the appropriate time.

4. ## RE: LastName, Firstname

Charlie,
I really don't understand why there are substr and len operators in your formula. I also don't understand the -rat part of the formula. When I have to deal with names, I usually have a last field and a first field. The formula to concatenate them would be alltrim(first)+" "+alltrim(last). If I want last name first I reverse them and put ", " in the middle.
I happen to be a Jr. myself and when I fill out forms that need the Jr. I simply put Urbanski, Jr. in the last name field.
I just looked back at your question and I'm not sure I understand if your situation has the names in two fields or one. I assume they're in two fields an you want to revrse the order. If they are in one field, that's beyond my skill level.

Jim

5. ## RE: LastName, Firstname

I just reread your post and see that you have only one field. There is a genie in the operators but I don't think that will address your situation. Sorry I can't be more help.

Jim

6. ## RE: LastName, Firstname

Well, I'm probably going to have to create a UDF in order to trap all the problems. The Word() function is the way to go - I did it the old fashioned way - brute force!

But I ran into people who used one letter as their first names "H. Ross Perot", some who had two or three middles initials and of course several Sr's, Jr's and III's.

Alpha is so good now, that I guess I now am at the point that I expect that there is a solution for everything that someone has thought of.

I'll get to work on that UDF!

Charlie

7. ## RE: LastName, Firstname

dim fullname as c
fullname="H. Ross Perot Jr."
?if(right(fullname,3)="Jr.".or.right(fullname,3)="Sr.",word(fullname,w_count(fullname)-1),word(fullname,w_count(fullname)))+" "+if(len(word(fullname,1))=1,word(fullname,1," ",2),word(fullname,1))+" "+word(fullname,w_count(fullname))
= "Perot H. Ross Jr"

Gabe

8. ## RE: LastName, Firstname

Thanks Gabe. This is great. I'm working on the function and will post it when I get it done with many thanks to you!

Charlie

9. ## RE: LastName, Firstname

Sorry, I vote for the udf.

nm1 = "Oscar De La Hoya"
nm2 = "John Ronald Reuel Tolkien"
nm3 = "John Paul II"

? if(right(nm1,3)="Jr.".or.right(nm1,3)="Sr.",word(nm1,w_count(nm1)-1),word(nm1,w_count(nm1)))+" "+if(len(word(nm1,1))=1,word(nm1,1," ",2),word(nm1,1))+" "+word(nm1,w_count(nm1))
= "Hoya Oscar Hoya"

? if(right(nm2,3)="Jr.".or.right(nm2,3)="Sr.",word(nm2,w_count(nm2)-1),word(nm2,w_count(nm2)))+" "+if(len(word(nm2,1))=1,word(nm2,1," ",2),word(nm2,1))+" "+word(nm2,w_count(nm2))
= "Tolkien John Tolkien"

? if(right(nm3,3)="Jr.".or.right(nm3,3)="Sr.",word(nm3,w_count(nm3)-1),word(nm3,w_count(nm3)))+" "+if(len(word(nm3,1))=1,word(nm3,1," ",2),word(nm3,1))+" "+word(nm3,w_count(nm3))
= "II John II"

10. ## RE: LastName, Firstname

Such an expression may not handle all of the scenarios which Stan laid out:

fullname="George H. W. Bush"
?if(right(fullname,3)="Jr.".or.right(fullname,3)="Sr.",word(fullname,w_count(fullname)-1),word(fullname,w_count(fullname)))+" "+if(len(word(fullname,1))=1,word(fullname,1," ",2),word(fullname,1))+" "+word(fullname,w_count(fullname))
= "Bush George Bush"

So a more complete UDF may be the way to go.

11. ## RE: LastName, Firstname

The underlying difficulty is that the structure of the original string is presumably not consistent from record to record.

Instead of just a firstname and a lastname, it's possible I suppose to actually need:

Title
Firstname
Middlename
Lastname
Suffix

Even if the data doesn't include titles you can't know in advance if a 3 word combination is first, middle, last or first, last, suffix.

Here are some tips I've found useful in the past:

If middlename is actually just an initial throughout all records then the length of the second term could be used to classify it.

If the word before the "last" word includes a trailing comma, then the last word is a suffix.

w_count() can be used to classify two word and 4 word combinations.

It's the three word combinations that always drove me nuts.

I've sometimes wondered if it would make sense to present a list of the 3 word strings and ask the user to tell you (a) is this f, m, l or is this f, l, s ?

-- tom

12. ## RE: LastName, Firstname

dim fullname as c
fullname="George H W Bush Jr."
?if(right(fullname,3)="Jr.".or.right(fullname,3)="Sr.",word(fullname,w_count(fullname)-1),word(fullname,w_count(fullname)))+" "+if(right(fullname,3)="Jr.".or.right(fullname,3)="Sr.",word(fullname,1," ",w_count(fullname)-2)+" "+right(fullname,3), word(fullname,1," ",w_count(fullname)-1))
= "Bush George H W Jr."

Any other wacky names??

Gabe

13. ## RE: LastName, Firstname

dim fullname as c
fullname="George H W Bush Jr."
?if(right(fullname,3)="Jr.".or.right(fullname,3)="Sr.",word(fullname,w_count(fullname)-1),word(fullname,w_count(fullname)))+" "+if(right(fullname,3)="Jr.".or.right(fullname,3)="Sr.",word(fullname,1," ",w_count(fullname)-2)+" "+right(fullname,3), word(fullname,1," ",w_count(fullname)-1))
= "Bush George H W Jr."

fullname="Oscar De La Hoeya"
?if(right(fullname,3)="Jr.".or.right(fullname,3)="Sr.",word(fullname,w_count(fullname)-1),word(fullname,w_count(fullname)))+" "+if(right(fullname,3)="Jr.".or.right(fullname,3)="Sr.",word(fullname,1," ",w_count(fullname)-2)+" "+right(fullname,3), word(fullname,1," ",w_count(fullname)-1))
= "Hoeya Oscar De La"

Any other wacky names??

Gabe

14. ## RE: LastName, Firstname

Any more??

dim fullname as c
fullname="Stan Steve Tom Cone Jr."
??if(right(fullname,3)="Jr.".or.right(fullname,3)="Sr.",word(fullname,w_count(fullname)-1),word(fullname,w_count(fullname)))+" "+if(right(fullname,3)="Jr.".or.right(fullname,3)="Sr.",word(fullname,1," ",w_count(fullname)-2)+" "+right(fullname,3), word(fullname,1," ",w_count(fullname)-1))
="Cone Stan Steve Tom Jr."

Gabe

15. ## RE: LastName, Firstname

Let's get really wacky:
dim fullname as c
fullname="Charles Gabe S Steve T Cone Jr."
?if(right(fullname,3)="Jr.".or.right(fullname,3)="Sr.",word(fullname,w_count(fullname)-1),word(fullname,w_count(fullname)))+" "+if(right(fullname,3)="Jr.".or.right(fullname,3)="Sr.",word(fullname,1," ",w_count(fullname)-2)+" "+right(fullname,3), word(fullname,1," ",w_count(fullname)-1))
="Cone Charles Gabe S Steve T Jr."

I am out of wacky names !!
Gabe

16. ## RE: LastName, Firstname

An alternative:

fullname="Charles S Steve T Cone, Jr."
?If(right(fullname,3)="Jr.".or.right(fullname,3)="Sr.",word(fullname,-2)+" "+word(fullname,1," ",w_count(fullname)-2)+" "+right(fullname,3), word(fullname,-1)+" "+word(fullname,1," ",w_count(fullname)-1))
= "Cone Charles S Steve T Jr."

Gabe

17. ## RE: LastName, Firstname

Yes,

there are other suffixes which commonly appear:

II
III
PhD
Md
Dvm
DDs
OD
JD

etc.

-- tom

18. ## RE: LastName, Firstname

Tom:

fullname="Gabe Charles S Steve T Cone, III"
?If(right(fullname,3)="Jr.".or.right(fullname,3)="Sr.".or.right(fullname,1)="I".or.right(fullname,1)="X",word(fullname,-2)+" "+word(fullname,1," ",w_count(fullname)-2)+" "+right(fullname,3), word(fullname,-1)+" "+word(fullname,1," ",w_count(fullname)-1))
= "Cone Gabe Charles S Steve T III"

MD, DO, PHd etc are titles not names
Gabe

19. ## RE: LastName, Firstname

Yes PhD is a title but I know lots of them who insist on using them. I even see things like Dr. Joseph K. Jones, MM. PhD.

It's particularly rampant in education

Jim

20. ## RE: LastName, Firstname

Blah Blah Blah, CPA ?

21. ## RE: LastName, Firstname

James/Stan:
I am sure they love to use their titles, but I doubt seriously that Charles has those in the first name or last name fields.

I could modify the expression to accommodate for that, but that would be an exercise in futility.

Gabe

Exactly.

23. ## RE: LastName, Firstname

Stan:
What I meant is, it could be done to allow for titles, but it would have no application as far as Charles is concerned, or any one else for that matter and as such, it's a waste of time to make these modification.

I can't see that anyone would insert titles in a field's name, be it first, middle or last names and if they do, the first thing they need to do, is separate the names from the title. Charles question had nothing to do with titles. That would be a different thread.
Gabe

24. ## RE: LastName, Firstname

Gabe,

in a perfect world I'd agree. Your point about "titles" not being part of a name is valid. My experience tells me however that in any given list of 1,000 names you're going to have folks who will include the title in their "official" name, and data entry people who will type it in rather than make a judgment call and risk alienating a customer, prospect, member, or donor. My point was not to criticize your expression, but to illustrate that "names" come in other shapes and sizes (so to speak).

I believe complex nested expressions can be a big help as your example illustrates. Coupled with careful checking of the resulting output a high percentage of correctly processed names is probable. Getting 100% correct without visual inspection is unlikely. -- tom

25. ## RE: LastName, Firstname

Tom:
In THIS thread, I am responding to Charles question about reversing the order of names and NOT to a HYPOTHETICAL question about names with titles tagged to them.

If you wish to ask the question (how to untangle a name that has titles in it, a very hypothetical situation), I will take a crack at it, but that was not the question and tackling such question, is not only deviating from the orginal question, but most likely would have no applicability. I have dealt with many "idiotic" situations, but have not seen somebody enter in the last name field, Smith CPA for instance. Give those secretaries some credit!

If anyone out there has this problem, please:
a-Fire your secretary or have him/her commited, and
b-Start a new thread

Gabe

26. ## RE: LastName, Firstname

Ok. I won't bother you again. Sorry.

-- tom

27. ## RE: LastName, Firstname

Gabe,

Many thanks on this expression.

Should we put it into a UDF or just leave it as it is?

Either way, I'll be happy to transfer this to the Code Archive.

You did a great job - one which I couldn't figure out except by the old style method forom my DBase days and this has helped me immensely in my app.

Charlie

28. ## RE: LastName, Firstname

Charles:
Glad it helped. You could leave it the way it is, or you could make a UDF out of it if you wish, only for aesthetic purposes.
Gabe

29. ## RE: LastName, Firstname

There was one glitch in that expression, evidently no one noticed it. I have made the necessary correction:

If(lastword(fullname)="Jr.".or.lastword(fullname)="Sr.".or.lastword(fullname)="II".or.lastword(fullname)="III".or.lastword(fullname)="IX".or.lastword(fullname)="V",word(fullname,-2)+" "+word(fullname,1," ",w_count(fullname)-2)+" "+right(fullname,3), word(fullname,-1)+" "+word(fullname,1," ",w_count(fullname)-1))

I am sure you could see it now!

Gabe

30. ## RE: LastName, Firstname

I will post the UDF called "NameConvert" to the Code Archive.

Gabe, I only added one small modification which others might chose to replace or ignore. I simply placed a comma after the lastname so that Charles H. Hoens III becomes Hoens, Charles H. III

Once again many thanks. This is what the Alpha Community is all about.

'Date Created: 06-Aug-2005 09:55:13 PM
'Last Updated: 06-Aug-2005 09:55:13 PM
'Created By : g gabriel "With the greatest of thanks!!!"
'Updated By : Charles Hoens
FUNCTION NameConvert AS C (Fullname AS C)
nameconvert=""
dim jac as c
jac=If(lastword(fullname)="Jr.".or.lastword(fullname)="Sr.".or.lastword(fullname)="II".or.lastword(fullname)="III".or.lastword(fullname)="IX".or.lastword(fullname)="V",word(fullname,-2)+", "+word(fullname,1," ",w_count(fullname)-2)+" "+right(fullname,3), word(fullname,-1)+" "+word(fullname,1," ",w_count(fullname)-1))

nameconvert=jac
END FUNCTION

Charlie

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•