How to Separate Text?

MikeSmith93 · 11/30/22

Hello,

I have a list of names and I want to separate them by last and first name. I am not seeing a function to accomplish this in Smartsheet like you would in excel.

=TEXTBEFORE(A2,",") and =TEXTAFTER(A2,",")

Full Name Last Name First Name

Barbosa, Fred Barbosa Fred

Any suggestions?

Ray Lindstrom · 11/30/22

@MikeSmith93,

Try this formula for your second. Props to another post where I found it after realizing the RIGHT function starts at the far right instead of moving right after a position.

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

https://community.smartsheet.com/discussion/90980/isolating-all-text-to-the-right-of-a-character-e-g-in-a-string

Hope this works for you! I tested using the same column names.

Ray Lindstrom · 11/30/22

Hi @MikeSmith93,

You could use the FIND function to find the comma, and then the PARSE function to grab the text. The find function will allow you to know the starting position within the string. Then if you needed, you could join/concatenate away!

FIND Function | Smartsheet Learning Center

https://help.smartsheet.com/function/find

Use this function to return the starting position of a string within text.

There are also LEFT and RIGHT functions that you can use to find the string text before and after a position. You can couple this with the FIND function. You'll need to "find" the position in order to use the LEFT and RIGHT functions.

Hope this helps!

All the best,

-Ray

MikeSmith93 · 11/30/22

Hi @Ray Lindstrom ,

For the first part of your response I am not sure how to do what you explained.

In the second part I utilized LEFT and was able to get the last names to work, but using RIGHT and the same setup doesn't work for the first name. I am not sure how to fix the First Name column.

Column Formula for Last Name: =LEFT([Full Name]@row, FIND(",", [Full Name]@row) - 1) *-1 was needed to remove the ,*

Column Formula for First Name: =RIGHT([Full Name]@row, FIND(",", [Full Name]@row))

Ray Lindstrom · 11/30/22

@MikeSmith93,

Try this formula for your second. Props to another post where I found it after realizing the RIGHT function starts at the far right instead of moving right after a position.

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

https://community.smartsheet.com/discussion/90980/isolating-all-text-to-the-right-of-a-character-e-g-in-a-string

Hope this works for you! I tested using the same column names.