Skip to main content
Question

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

  • November 2, 2022
  • 0 replies
  • 150 views
Forum|alt.badge.img+6
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
------------------------------
This topic has been closed for replies.

MarkShnierYou
Forum|alt.badge.img+24
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
------------------------------
Mark Shnier. Your Quickbase Coach
rbuschmeyer
Forum|alt.badge.img+2
  • rbuschmeyer
  • Registered
  • November 3, 2022
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
------------------------------
Forum|alt.badge.img+5
  • SimonH
  • Registered
  • November 3, 2022
anytime a question about names comes up, these articles come to mind

https://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/

And perhaps the most famous XKCD
https://xkcd.com/327/

------------------------------
Simon H
------------------------------
Terms & ConditionsAccessibility statement