extra space and a comma to remove
![OshaK](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
I'm using this formula to combine Last, First Names with the Middle name initial (if it exists) and the credentials:
=[Last Name]@row + ", " + [First Name]@row + " " + [Middle Initial]@row + ", " + [Degree(s)]@row
If there is a Middle name initial, there is no problem and it displays fine. However, if there is no initial, I get the extra space and a comma. Is it possible to make this formula conditional and ONLY display the extra space and comma when there is an initial?
Thank you
Answers
-
-
thank you so much, @James Hokamp! Any chance you can copy/paste the formula to this conversation? Also, I'm sorry, I need one more condition for the Degrees column because not everyone has the degrees listed. So the commas and spaces should only appear if there is either an initial or degrees or both, and no spaces and commas if none is present, and a single comma, space if one of them is present.
Thank you!
-
Hi @OshaK
I hope you're well and safe!
We could use JOIN COLLECT, but would it work if we moved the columns around so they would be?
Last Name, First Name, Middle Initial, Degree(s)
Is that an option?
I hope that helps!
Be safe, and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
A substitute join would be cleaner i think.
if the columns are arranged as you said, Last Name, First Name, Middle Initial, Degree(s)
=substitute(join([Last Name]@row:[Degree(s)]@row,", "),", ,",",")
vs
=join(collect([Last Name]@row:[Degree(s)]@row,[Last Name]@row:[Degree(s)]@row,not(isblank(@cell))),", ")
-
this is great, thank you, @Andrée Starå , it's perfect!
-
@Andrée Starå, thank you again, it works great, except when there is no initial nor degrees.
When there are just last and first names, there is an extra comma. Is there a way to remove it automatically if there are only last and first names?
so instead of
Doe, Osha,
can it be
Doe, Osha
?
thank you!
-
Then @Andrée Starå's initial suggestion would be optimal. I assumed you were forcing the user to enter those values.
=join(collect([Last Name]@row:[Degree(s)]@row,[Last Name]@row:[Degree(s)]@row,not(isblank(@cell))),", ")
-
@L_123 thank you, that works! How would I develop this formula further so I can have a break line?
I need it to look like that:
Last Name, First Name
Degree 1, Degree2
Degree 3
(I need it to generate the document, where the formatting is important. Thank you!
-
you need to separate it out into 3 separate statements and use char(10) between them
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!