Split First and Last Names

Steven_Hambleto · ‎May 18, 2017

Hi all,

I want to use Zapier to export my table to Campaign Monitor but I only need the first name from my primary field which includes the person’s full name (first_name last_name).

Any ideas how I can accomplish this in the table or via Zapier?

I don’t mind creating a first name field and hiding it.

Matt_Sayward · ‎May 19, 2017

Y’know, It would actually be pretty neat if Name was a field type, and if Airtable parsed the input from it to figure out stuff like first, middle and surnames.

For example:

If one space occurs in the field, treat all characters to the left of the space as the first name and all characters to the right as a surname.

If two spaces occur, treat all characters to the left of the first space as the first name, between the first space and second space as the middle name, and all the characters to the right of the second space as the surname…

Then you could call on that in formulas.

=concatenate("Hello ", {Name}, ‘first’, “!” )

Hashim_Warren · ‎May 19, 2017

Use Zapier Formatter

zapier.com/help/formatter/#text

W_Vann_Hall · ‎May 20, 2017

Well, if you’re certain your full_name field is reasonably consistent, you can always use

LEFT(full_name, SEARCH(" ",full_name)-1)

to extract the first name and store it in a first_name field. Where it gets tricky is extracting the last name if your full_name field includes middle names, name suffixes, academic or professional qualifications, etc. (If it includes honorifics – Dr., Rev., and the like – it makes extracting first names messy.)

Back in the Dark Ages, we used to store names in last_name~first_name middle_name order, with a tilde, caret, or other character not likely to be found in a name serving as a ‘swap’ character. This allowed us to sort by last name without having to have a separate last_name field while still being able to print “Dear John” letters. (Oddly, for some reason “last_name~first_name” seemed easier for people to enter without error than “first_name”[TAB]“last_name”.)

Unfortunately, to be completely accurate, you almost always must have a human ‘hinting’ the name; otherwise, there will be names that trip up your algorithm:

Gabriel Garcia Márquez
U Thant
Mao Tse-Tung
Alfred, Lord Tennyson
Prince
Kiefer William Frederick Dempsey George Rufus Sutherland

Vann

Steven_Hambleto · ‎May 25, 2017

That worked thank you!

Francis_Vital · ‎Jul 13, 2017

What would the formula to extract the last_name look like?

W_Vann_Hall · ‎Jul 16, 2017

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

Jeremy_Chevalli · ‎Nov 21, 2018

Worked like a charm for extracting email domains:

REPLACE({Email},1,FIND("~",SUBSTITUTE({Email},"@","~",LEN({Email}) - LEN(SUBSTITUTE({Email},"@","")))),"")

Dora_Salinas · ‎Jan 14, 2019

Thanks for that Jeremy!!! You rock!

Mark_Haynes · ‎May 02, 2019

Hey, guys -

Thanks for all of this great info - the formulas have been very helpful! The First Name extractor as written returns “Jo” from the string “Jo Ann Doe”. For the life me, I can’t seem to wrap my head around how to get the formula to return “Jo Ann” rather than just “Jo”. I’m also assuming that since the input value has been trimmed, for a regular two-word name, it would still just return the first.

Thanks in advance for any help!

Cheers,
Mark