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.
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’, “!” )
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
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
That worked thank you!
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
What would the formula to extract the last_name look like?
What would the formula to extract the last_name look like?
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("~", FImarked 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( acextracted 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.)
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("~", FImarked 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( acextracted 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.)
Worked like a charm for extracting email domains:
REPLACE({Email},1,FIND("~",SUBSTITUTE({Email},"@","~",LEN({Email}) - LEN(SUBSTITUTE({Email},"@","")))),"")
Worked like a charm for extracting email domains:
REPLACE({Email},1,FIND("~",SUBSTITUTE({Email},"@","~",LEN({Email}) - LEN(SUBSTITUTE({Email},"@","")))),"")
Thanks for that Jeremy!!! You rock!
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
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
Welcome to the community, Mark! To keep all parts except the last name, it’s only a slight variation of the formula that @W_Vann_Hall posted above to keep only the last name:
REPLACE({Name},FIND("~",SUBSTITUTE({Name}," ","~",LEN({Name}) -
LEN(SUBSTITUTE({Name}," ","")))),LEN({Name},"")Welcome to the community, Mark! To keep all parts except the last name, it’s only a slight variation of the formula that @W_Vann_Hall posted above to keep only the last name:
REPLACE({Name},FIND("~",SUBSTITUTE({Name}," ","~",LEN({Name}) -
LEN(SUBSTITUTE({Name}," ","")))),LEN({Name},"")Thanks, Justin!!! You rock! 
Is there a way to do this to extract just the street numbers from an address? like 1234 sesame St. Can I get just 1234
My column is called site address
Is there a way to do this to extract just the street numbers from an address? like 1234 sesame St. Can I get just 1234
My column is called site address
If the numbers are always at the beginning you can search for the space and extract the left part
I’ve done a bad job of updating earlier posts, but version 3 of my name processing routines can be found here.
And those dealing with patronymics and matronymics may want to check the formula and base referenced here.
I am having a really hard time getting my Names column to split into First names, Last name
In the Names Column I would add the name in this format:
Danger, Bob & Lucy
Jitter, Bug
Then I need to write formula’s to get the names into their respective columns.
I used this Formula, to grab the Last name…
LEFT(Name, SEARCH(",",Name)-1)
but after that, I can’t get it to work right!
Could someone plz help? Thank you!!!
(I tried some diff variations using RIGHT instead of LEFT… lol and that didn’t work either…)
I am having a really hard time getting my Names column to split into First names, Last name
In the Names Column I would add the name in this format:
Danger, Bob & Lucy
Jitter, Bug
Then I need to write formula’s to get the names into their respective columns.
I used this Formula, to grab the Last name…
LEFT(Name, SEARCH(",",Name)-1)
but after that, I can’t get it to work right!
Could someone plz help? Thank you!!!
(I tried some diff variations using RIGHT instead of LEFT… lol and that didn’t work either…)
You have the formula for {Last Name}; for {First Name} use
RIGHT(
Name,
LEN(
Name
)-FIND(
',',
Name
)-1
)
P.S. I think I want to meet your friends! :winking_face:
You have the formula for {Last Name}; for {First Name} use
RIGHT(
Name,
LEN(
Name
)-FIND(
',',
Name
)-1
)
P.S. I think I want to meet your friends! :winking_face:
THANK YOU!!! That worked! :grinning_face_with_big_eyes:
I can never wrap my head around the LEN functions… doesn’t matter how many times I study it… My brain just seems to fuzz and thats it.
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("~", FImarked 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( acextracted 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.)
I was playing around with this for my own purposes, and came up with this. If you have very clean first and last name, the function below can be used to get last name.
RIGHT(fullname, LEN(fullname)-SEARCH(" ", fullname))
I was playing around with this for my own purposes, and came up with this. If you have very clean first and last name, the function below can be used to get last name.
RIGHT(fullname, LEN(fullname)-SEARCH(" ", fullname))
Here’s yet another way to pull a last name, assuming you’ve got a field extracting the first name, and that it’s only a two-part name:
SUBSTITUTE({Full Name}, {First Name} & " ", "")Hey, regarding first name, a formula that also takes into account that the full name was filled partially and may include only the first name would look like:
IF(SEARCH(" “,{Full_Name}) > 0, LEFT({Full_Name}, SEARCH(” ",{Full_Name})-1), {Full_Name})
This formula will not result in an error if there are no spaces in full name.
This works fine for all fields except lookup fields for me. So when I try to split first and last names from a lookup field, its returning errors. Any ideas?
This works fine for all fields except lookup fields for me. So when I try to split first and last names from a lookup field, its returning errors. Any ideas?
A lookup field returns an array, not a string, and doesn’t auto-convert to a string when a formula asks for it. To convert an array to a string, concatenate it with an empty string. In your formula, anywhere that you have:
{Client Name}
you will need to change it to:
{Client Name} & ""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("~", FImarked 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( acextracted 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.)
As this is directly regarding the codes in @W_Vann_Hall’s demo base in this thread I’ll ask my question here rather than to start a new topic.
My {WholeName} field follow the format “FirstName MiddleName LastName (neé MaidenName)”.
I want to break it down to four fields
I can get it to return 1-3 correctly as long as there is no maiden name, if there is it no longer provides the correct last name. No supprise there.
For my purposes I assume it would be best to treat (neé MaidenName) as a suffix and by that logic remove it in {TrimSuffix}.
I tried the following code to strip the MaidenName-part
SUBSTITUTE(
IF(
OR(FIND(" Jr",TrimName)=LEN(TrimName)-3,
FIND(" Sr",TrimName)=LEN(TrimName)-3,
FIND(" II",TrimName)=LEN(TrimName)-3,
FIND(" IV",TrimName)=LEN(TrimName)-3),
LEFT(TrimName,LEN(TrimName)-4),
IF(
OR(FIND(" Jr.",TrimName)=LEN(TrimName)-4,
FIND(" Sr.",TrimName)=LEN(TrimName)-4,
FIND(" III",TrimName)=LEN(TrimName)-4),
LEFT(TrimName,LEN(TrimName)-5),
IF(
FIND("(",TrimName),
LEFT(TrimName, SEARCH("(", TrimName)-1),
TrimName))),",",""
)
It removes the whole maiden name part okay, but now {Last Name} no longer outputs a last name at all where a maiden name is present in {WholeName}. Any and all help to adjust this would be greatly appreciated. If I can achieve a last name here then I can get the fourth field, maiden name, on my own.
Hey guys, thanks a lot for great tips!
How would the formula look like if I want to get first name and the first letter of the last name? The Name field is pretty uniform with First and Last name, no middle names.
Thanks in advance!
Hey guys, thanks a lot for great tips!
How would the formula look like if I want to get first name and the first letter of the last name? The Name field is pretty uniform with First and Last name, no middle names.
Thanks in advance!
Welcome to the community, @Meerim_Asylbekova! :grinning_face_with_big_eyes: This should work, replacing “Name” with your actual field name:
IF(Name, REGEX_EXTRACT(Name, ".* ."))
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.