Split First and Last Names

[Steven_Hambleto]

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.

[Ronen_Babayoff]

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.

[Michael_Fisk]

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?

[Justin_Barrett]

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} & ""

[momentsgoneby80]

W_Vann_Hall:

REPLACE({Name},1,FIND("~",SUBSTITUTE({Name}," “,”~",LEN({Name}) - LEN(SUBSTITUTE({Name}," “,”")))),"")

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

1. First name
2. Middle name
3. Last name
4. Maiden name

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.

[Meerim_Asylbeko]

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!

[Justin_Barrett]

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, ".* ."))

[Meerim_Asylbeko]

This worked like a charm! Thank you so much, Justin!

[Mike_Thorington]

Justin_Barrett:

REPLACE({Name},FIND("~",SUBSTITUTE({Name}," ","~",LEN({Name}) - 
LEN(SUBSTITUTE({Name}," ","")))),LEN({Name},"")

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},"")

[Mike_Thorington]

Nevermind, I found it.

This worked:

REPLACE({Name},FIND("~",SUBSTITUTE({Name}," “,”~",LEN({Name}) -

LEN(SUBSTITUTE({Name}," “,”")))),LEN({Name}),"")

[Mike_Thorington]

W_Vann_Hall:

REPLACE({Name},1,FIND("~",SUBSTITUTE({Name}," “,”~",LEN({Name}) - LEN(SUBSTITUTE({Name}," “,”")))),"")

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

@W_Vann_Hall