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.
-
-
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))
-
Perfect - that worked! Your the best!! :)
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 439 Global Discussions
- 138 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!