Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: Split First and Last Names

4386 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Steven_Hambleto
6 - Interface Innovator
6 - Interface Innovator

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.

41 Replies 41
Ronen_Babayoff
7 - App Architect
7 - App Architect

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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?

Screenshot 2020-05-28 11.42.13 Screenshot 2020-05-28 11.42.20

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

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
4 - Data Explorer
4 - Data Explorer

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

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

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

Nevermind, I found it.

This worked:

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

LEN(SUBSTITUTE({Name}," “,”")))),LEN({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