Split First and Last Names

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

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

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

Worked like a charm for extracting email domains:

REPLACE({Email},1,FIND("~",SUBSTITUTE({Email},"@","~",LEN({Email}) - LEN(SUBSTITUTE({Email},"@","")))),"")

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

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

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.

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("~", [[marked 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( [[extracted 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.)

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?

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.

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?

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

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

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?

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

What would the formula to extract the last_name look like?