That’s a little more difficult…
At heart, the formula would be something like this:
REPLACE({Name},1,FIND("~",SUBSTITUTE({Name}," ","~",LEN({Name}) - LEN(SUBSTITUTE({Name}," ","")))),"")
That’s a little hard to suss out at first glance, so let me break it down, back to front:
LEN({Name}) - LEN(SUBSTITUTE({Name}," ",""))
gets the difference in length between {Name} and {Name} with all spaces stripped. This gives us the index of the last space in the name, which presumably prefaces the last name.
SUBSTITUTE({Name}," ","~", [[calculated index]] )
replaces the last space in the name with a “~” character, simply as a marker, and
REPLACE({Name},1,FIND("~", [[marked name string]] ),"")
deletes everything in the name up through the “~” marker, leaving behind the last name…
…maybe.
The problem comes if any names in your database have suffixes: Jr, Sr., IV, M.D., and so forth. If you’re not careful, you can end up with a lot of ‘Dear Mr. Jr’ letters. To avoid this, you should create an intermediary field and use it to strip out common suffixes. There’s probably a more elegant way to do this, but here’s a formula that removes Juniors, Seniors, and Is through IVs:
IF(
OR(
FIND(" Jr",{Name})=LEN({Name})-3,
FIND(" Sr",{Name})=LEN({Name})-3,
FIND(" II",{Name})=LEN({Name})-3,
FIND(" IV",{Name})=LEN({Name})-3),
LEFT({Name},LEN({Name})-4),
IF(
OR(
FIND(" Jr.",{Name})=LEN({Name})-4,
FIND(" Sr.",{Name})=LEN({Name})-4,
FIND(" III",{Name})=LEN({Name})-4),
LEFT({Name},LEN({Name})-5),
{Name}))
Essentially, this looks for common suffixes preceded by a space falling at the end of the name and, if found, removes them. Feed the output of this routine into the earlier last-name-extraction formula, and you should have clean last names. (Hide this column to avoid clutter.)
If you have additional suffixes in your database, add them to this formula to extract. For 2- or 3-letter suffixes, add them inside the appropriate OR()
block; longer suffixes (‘M.D.’, ‘Ph.D.’, and the like) will need their own IF()
blocks added.
Since the suffix-stripper matches at the end of the name, it will be broken by trailing spaces. If there is a chance your data has any, you will want to TRIM({Name})
first (another column to hide!) and feed its output into the suffix-stripper.
Finally, if any of your 'Jr.'s and 'Sr.'s are actually ', Jr.'s and ', Sr.'s, you can wrap the last-name-extraction formula in SUBSTITUTE( [[extracted last name]] ,",","")
.
All of that is clear as mud, I know, so I’ve tossed together a sample base here. (The formulas are documented in the field descriptions.)