Formula for separating names

Hello all i have a smart sheet that was imported from excel and need help. Currently I only have the full name in one cell (i.e) John doe. I want to have it where it will split the first and last name into the next two cells in the same row. And then I need it to take either the full name or the two separated names and create an email for it.

Example:

Column 1: full name: John Doe

Column 2: first name: John

Column 3: Last Name: Doe

Column 4: Email: John.doe@XYZ.com

and all of these are in the same row. I then need to take that email and have it fill a cell to a different smartsheet with other data on it but I usually use a vlookup with that. If there is a better way to do all of this with one or two formulas then please submit! :)

Find more posts tagged with

API and Developers

Formulas

Automations

Cell linking

Accepted answers

All comments

Peggy Parchert

Hello @Jeremy Oesch

This is what I use currently to separate/create:

Screenshot 2023-03-07 at 12.47.29 PM.png

Formulas:

First Name: =LEFT([Full Name]@row, FIND(" ", [Full Name]@row) - 1)

Last Name: =RIGHT([Full Name]@row, FIND(" ", [Full Name]@row) - 1)

Email address Helper: =JOIN([First Name]@row:[Last Name]@row, ".") + "@XYZ.com"

*this is if the email addresses will all be the same

You can make each of the above into column formula columns and any new entries will automatically complete the separation/creation.

Jeremy Oesch

Thank you @Peggy Parchert! is there a way to do it with less formulas?

Best,

Jeremy

Peggy Parchert

@Jeremy Oesch - I do not know of a simpler way to complete what you are asking for without each of the formulas I've listed above.

@Andrée Starå or @Paul Newcome - is there a way to complete this using less formulas?

Paul Newcome

There is no way to do it with less formulas because you have to have a formula in each cell that you want automatically calculated.

Jeremy Oesch

@Paul Newcome @Peggy Parchert Thank you guys very much for the help!

Best,

Jeremy

Paula_

The above formulas worked fine for the first name, but I can get the last name to be correct. It comes back with an odd portion of the name. Suggestions?

Paul Newcome

@Paula_ Try this for last name:

=RIGHT([Contact Name]@row, LEN([Contact Name]@row) - FIND(" ", [Contact Name]@row))

Paula_

Perfect - that worked! Your the best!! :)

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.