Forum Discussion

TroyMacPherson's avatar
TroyMacPherson
Qrew Cadet
3 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(...
Formulas and functions
MarkShnier__You's avatar
MarkShnier__You
Icon for Qrew Legend rankQrew Legend
3 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
    3 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
      3 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
      ------------------------------