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

------------------------------
Troy MacPherson
------------------------------

### 6 Replies

• 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@gmail.com
------------------------------
• Thanks Mark, I'll try that.

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

------------------------------
Troy MacPherson
------------------------------
• 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
------------------------------
• 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
------------------------------
• That's what I was thinking also.   This is why on many forms,  first, last, middle and suffix are separate fields.

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