Alpha Video Training
Page 1 of 2 12 LastLast
Results 1 to 30 of 32

Thread: LastName, Firstname

  1. #1
    "Certified" Alphaholic Charles Hoens's Avatar
    Real Name
    Charles Hoens
    Join Date
    Sep 2000
    Location
    Princeton, NJ
    Posts
    1,170

    Default 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. #2
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default 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. #3
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default 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. #4
    Member
    Real Name
    James Urbanski
    Join Date
    May 2000
    Location
    New Port Richey, FL
    Posts
    620

    Default 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. #5
    Member
    Real Name
    James Urbanski
    Join Date
    May 2000
    Location
    New Port Richey, FL
    Posts
    620

    Default 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. #6
    "Certified" Alphaholic Charles Hoens's Avatar
    Real Name
    Charles Hoens
    Join Date
    Sep 2000
    Location
    Princeton, NJ
    Posts
    1,170

    Default 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. #7
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default 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. #8
    "Certified" Alphaholic Charles Hoens's Avatar
    Real Name
    Charles Hoens
    Join Date
    Sep 2000
    Location
    Princeton, NJ
    Posts
    1,170

    Default 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. #9
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default 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. #10
    Member
    Real Name
    Steve Andrews
    Join Date
    Jan 2004
    Location
    Mahwah, NJ
    Posts
    644

    Default 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. #11
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,310

    Default 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. #12
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default 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. #13
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default 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. #14
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default 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. #15
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default 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. #16
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default 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. #17
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,310

    Default RE: LastName, Firstname

    Yes,

    there are other suffixes which commonly appear:

    II
    III
    PhD
    Md
    Dvm
    DDs
    OD
    JD

    etc.

    -- tom

  18. #18
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default 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. #19
    Member
    Real Name
    James Urbanski
    Join Date
    May 2000
    Location
    New Port Richey, FL
    Posts
    620

    Default 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. #20
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default RE: LastName, Firstname

    Blah Blah Blah, CPA ?

  21. #21
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default 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

  22. #22
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default RE: LastName, Firstname

    Exactly.

  23. #23
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default 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. #24
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,310

    Default 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. #25
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default 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. #26
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,310

    Default RE: LastName, Firstname

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

    -- tom

  27. #27
    "Certified" Alphaholic Charles Hoens's Avatar
    Real Name
    Charles Hoens
    Join Date
    Sep 2000
    Location
    Princeton, NJ
    Posts
    1,170

    Default 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. #28
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default 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. #29
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default 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. #30
    "Certified" Alphaholic Charles Hoens's Avatar
    Real Name
    Charles Hoens
    Join Date
    Sep 2000
    Location
    Princeton, NJ
    Posts
    1,170

    Default 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

Similar Threads

  1. Lastname Firstname Nameconvert Function
    By Charles Hoens in forum Code Archive
    Replies: 0
    Last Post: 08-06-2005, 07:07 PM
  2. lastname() source
    By jkukuda in forum Alpha Five Version 6
    Replies: 13
    Last Post: 05-14-2005, 12:09 AM

Bookmarks

Posting Permissions

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