Formula to separate first and last name from Contact List

Hi everyone! I'm trying to separate Last Name and First Name from a Contact Column. I'm CLOSE, but it's not QUITE working. I tried to take a formula I saw on another thread, but because I don't actually have an understanding of what the formula is actually doing, I'm unable to trouble shoot why it's wrong. It seems to be working for the Last name, but not for the first name. Can anyone give advice or explain to me why it's not working quite right? Here is what I am using for formula:

For Last Name column:

=RIGHT(Volunteer@row , LEN(Volunteer@row ) - FIND(" ", Volunteer@row ))

For First Name Column:

=LEFT(Volunteer@row , LEN(Volunteer@row ) - FIND(" ", Volunteer@row ))

Here is what it is doing:

Find more posts tagged with

Accepted answers

jmyzk_cloudsmart_jp

Hi @RiseUpPNW

You need to change the First Name's formula to this;

=LEFT(Volunteer@row , FIND(" ", Volunteer@row ) - 1)

In English, find the space between the first name and last name. Get the string from the top up to position one before the found space position to get the first name.

https://app.smartsheet.com/b/publish?EQBCT=e1858249b1eb43d08b95fe92860b5cd5

All comments

Bek T

Try this for your first name ('column 19 being the contact list column):

=LEFT([Column19]@row , FIND(" ", [Column19]@row ))

and this for your surname:

=RIGHT([Column19]@row , LEN([Column19]@row ) - FIND(CHAR(10), SUBSTITUTE([Column19]@row , " ", CHAR(10), LEN([Column19]@row ) - LEN(SUBSTITUTE([Column19]@row , "-", "")))))

The latter was from another smartsheet question, the top from AI; I've tested with some very long names and it seems to work, hopefully it does when you test it out!

jmyzk_cloudsmart_jp

Hi @RiseUpPNW

You need to change the First Name's formula to this;

=LEFT(Volunteer@row , FIND(" ", Volunteer@row ) - 1)

In English, find the space between the first name and last name. Get the string from the top up to position one before the found space position to get the first name.

https://app.smartsheet.com/b/publish?EQBCT=e1858249b1eb43d08b95fe92860b5cd5

RiseUpPNW

Thank you @jmyzk_cloudsmart_jp! And thank you for the breakdown of HOW it works!

jmyzk_cloudsmart_jp

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.