May 18, 2017 09:51 PM
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.
Jul 31, 2019 02:27 PM
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.
May 28, 2020 11:42 AM
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?
May 28, 2020 02:12 PM
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} & ""
Mar 20, 2021 08:25 AM
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.
Aug 11, 2021 10:04 AM
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!
Aug 11, 2021 05:01 PM
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, ".* ."))
Aug 12, 2021 06:01 AM
This worked like a charm! Thank you so much, Justin!
May 26, 2022 12:39 PM
Hello - I am trying to use the above formula to extrat all parts except the Last Name. I keep running into an an error that is referencing an unclosed parenthesis.
The formula has 7 left parenthesis but only 6 right. I have tried to insert a closing parenthesis in areas that I thought were logical but still keep coming up with an error. Where does the closing parenthesis go.
REPLACE({Name},FIND("~",SUBSTITUTE({Name}," “,”~",LEN({Name}) -
LEN(SUBSTITUTE({Name}," “,”"))))),LEN({Name},"")
May 26, 2022 12:42 PM
Nevermind, I found it.
This worked:
REPLACE({Name},FIND("~",SUBSTITUTE({Name}," “,”~",LEN({Name}) -
LEN(SUBSTITUTE({Name}," “,”")))),LEN({Name}),"")
May 26, 2022 12:55 PM
Question for you that is related to this, but almost in the opposite direction.
We have 2 email address, essentially. We have a standard first.last@email.com and first.last@sso.com (for single sign on purposes). Could I use a formula in the SSO column to copy over the first.last@ from the email column and finish it with first.last@sso.com
May 26, 2022 07:01 PM
Unfortunately @W_Vann_Hall hasn’t logged into the forum in nearly two years. We hope that he’s all right, but the likelihood of him replying is slim, so I’m going to chime in on this.
In short: yes, this is possible. Instead of copying the first part, though, it’s easier to replace the domain:
SUBSTITUTE(Email, "@email.com", "@sso.com")
May 27, 2022 03:15 PM
Thank you, that worked kind of. The issue I have now is that not all domains in the email column are the same. How can I account for the not as commonly used oddball email domain contained in the email field. All domains in the sso field are still the same fyi. Thanks again for your help.
May 27, 2022 10:36 PM
Thanks for the clarification. I got the impression that those domains were all identical. If not, then you can use a regular expression to replace any domain:
IF(Email, REGEX_REPLACE(Email, "@.*", "@sso.com"))
Aug 05, 2022 02:38 PM
The above is great, but I don’t care about suffixes. Just how to split the first and last name. How do I do that?
Aug 09, 2022 06:56 PM
First name:
IF({Full Name}, REGEX_EXTRACT({Full Name}, "[^ ]*"))
Last name:
IF({Full Name}, REGEX_EXTRACT({Full Name}, "[^ ]*$"))
Both of these use the [^ ]*
combination, which captures any number of non-space characters. By default the source string is parsed from left to right, so the first expression will stop once it hits the space between the first and last name. The $
at the end of the second expression tells the parser to begin at the end of the string, effectively working backwards until it hits the same separating space. Because the space is ignored in both cases, there’s no need to trim anything.
Aug 15, 2022 09:07 AM
This response is really helpful! The challenge I’m running into is that many of the people on my spreadsheet have multiple last names. Which means that when I use this formula it only pulls out the very first and very last names listed. For example:
Is there a different way to write this formula that will include all of the last names?
Aug 15, 2022 08:29 PM
The easiest way to do that is to remove the first name and its trailing space from the original string. Change the formula for the {Last}
field to this:
IF(AND({Full Name}, First), SUBSTITUTE({Full Name}, First & " ", ""))
Aug 16, 2022 07:03 AM
Worked like a charm! Thank you
Sep 17, 2022 05:31 PM
In regards to the formula for using it lookup fields, what if you have multiple items in the look up field as such. How would you bring in second or third name if possible?
Sep 17, 2022 08:21 PM
Split the names in the original table. Then lookup the individual names.