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

Question

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------------------------------

markshnier__you · Answer

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&nbsp;Michael J. Fox------------------------------Mark Shnier (Your Quickbase Coach)mark.shnier@gmail.com------------------------------

troymacpherson · Answer

Thanks Mark, I'll try that.

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

johncrosland · Answer

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
------------------------------

rbuschmeyer · Answer

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 · Answer

That's what I was thinking also.&nbsp; &nbsp;This is why on many forms,&nbsp; first, last, middle and suffix are separate fields.------------------------------Jeff Peterson------------------------------

Forum Discussion

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