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.

[Justin_Barrett]

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

[Mike_Thorington]

Justin_Barrett:

SUBSTITUTE(Email, "@email.com", "@sso.com")

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.

[Justin_Barrett]

Mike_Thorington:

The issue I have now is that not all domains in the email column are the same.

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

[Elaine_Winter]

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?

[Justin_Barrett]

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.

[Ashley_Tarbet_D]

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?

[Justin_Barrett]

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

[Ashley_Tarbet_D]

Worked like a charm! Thank you

[Nicole_Merwin]

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?

[kuovonne]

Nicole_Merwin:

what if you have multiple items in the look up field

Split the names in the original table. Then lookup the individual names.