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! :)
Answers
-
Hello @Jeremy Oesch
This is what I use currently to separate/create:
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 - 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?
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
-
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?
-
@Paula_ Try this for last name:
=RIGHT([Contact Name]@row, LEN([Contact Name]@row) - FIND(" ", [Contact Name]@row))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Perfect - that worked! Your the best!! :)
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 438 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!