Forum Discussion

TroyMacPherson's avatar
TroyMacPherson
Qrew Cadet
2 years ago

Formula to pull Last name from a full name field accounting for jr, sr, II, etc.

Hi QB users,

I have a QB with a simple formula that pulls the last name from a [Full Name] field, which works most of the time except for suffixes like Sr., Jr. II, etc.

Current formula:
Right([Full name]," ")

I guess I could write an if statement for each case, but I'm not good with trim, part or left/right, so could use help on the syntax to get the second to last "name" in those cases.

Thanks in advance,

------------------------------
Troy MacPherson
------------------------------
Formulas and functions

6 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew #1 Challenger
    2 years ago
    try
    NotLeft([Full Name], " ")

    It will be better but you will still have issues because you may have data with a middle name or initial like 

    Michael J. Fox

    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------
    • TroyMacPherson's avatar
      TroyMacPherson
      Qrew Cadet
      2 years ago

      Thanks Mark, I'll try that.

      You're right, some of my Full Name entries have 3-4 names...



      ------------------------------
      Troy MacPherson
      ------------------------------
      • JohnCrosland's avatar
        JohnCrosland
        Qrew Trainee
        2 years ago
        I would recommend using the Length and Part formulas. So, it would be something like If(Length(Part([Name], 4, " "))>2, Part([Name], 4 " "), Length(Part([Name], 3, " "))>2, Part([Name], 3, " "), Part([Name], 2, " "))

        ------------------------------
        John Crosland
        ------------------------------
  • RyanBuschmeyer1's avatar
    RyanBuschmeyer1
    Qrew Trainee
    2 years ago
    So an interesting question is "what is the nature of input" of the Full Name field?  Is that a user-input field?  If so, (and if it is possible in your scenario), it might be easier to normalize the data through a different user-input strategy (e.g. different fields for each name component). 

    Seems the parsing of the last name could be extremely problematic given you could have several different styles of ethnic naming.

    Sorry if I'm not providing an acceptable solution...just some thoughts.

    ------------------------------
    Ryan Buschmeyer
    ------------------------------
    • JeffPeterson1's avatar
      JeffPeterson1
      Qrew Captain
      2 years ago
      That's what I was thinking also.   This is why on many forms,  first, last, middle and suffix are separate fields.

      ------------------------------
      Jeff Peterson
      ------------------------------