combining cells for a report
Good afternoon. I am trying to combine information from a single row but multiple cells into a single cell on a report. I need this information to show as a list on the report.
So for example my spread sheet has:
I used a simple formula (=[Item 1]@row + " " + [Color 1]@row + "," + [Item 2]@row + " " + [Color 2]@row + "," + [Item 3]@row + " " + [Color 3]@row) to combine the columns and tried wrap text in the Combined column but I am not getting the result i want visually for my report.
I added a column "Desired Result" so you can see how I want it to look, but only got that result by manually typing in the information to the cell and playing with the width to get it to look correct.
Ideally I need the Combined column to wrap the text after the "," or something similar.
Best Answer
-
Hello, I honestly didn't think this would be possible. Smartsheet formulas don't have a newline code (such as "/n") like some programming languages do. However, I did some tinkering and found success with the line-break keyboard shortcut (Ctrl + Enter) which is used to make multiline cells. The line break inserted inside the cell can be copy and pasted. So your current formula should work. You'll just need to copy the line break and paste it behind the comma inside your quotes. Using Ctrl + Enter directly in the formula didn't work for me. I had to copy and paste while typing in a cell.
To highlight the line break: Press Ctrl + Enter while having a cell 'active/open', then with the cursor on bottom line of cell, hold shift and press left arrow to highlight the line break from bottom to top line. Hit Esc to exit active cell, then in the formula cell, place your cursor inside of string quotes in your formula and past. Alternatively, copy and pasting the below formula may preserve the line break. If not, hopefully my explanation makes enough sense to replicate on your own.
=[Item 1]@row + " " + [Color 1]@row + ",
" + [Item 2]@row + " " + [Color 2]@row + ",
" + [Item 3]@row + " " + [Color 3]@row
Hope this helps!
Answers
-
Hello, I honestly didn't think this would be possible. Smartsheet formulas don't have a newline code (such as "/n") like some programming languages do. However, I did some tinkering and found success with the line-break keyboard shortcut (Ctrl + Enter) which is used to make multiline cells. The line break inserted inside the cell can be copy and pasted. So your current formula should work. You'll just need to copy the line break and paste it behind the comma inside your quotes. Using Ctrl + Enter directly in the formula didn't work for me. I had to copy and paste while typing in a cell.
To highlight the line break: Press Ctrl + Enter while having a cell 'active/open', then with the cursor on bottom line of cell, hold shift and press left arrow to highlight the line break from bottom to top line. Hit Esc to exit active cell, then in the formula cell, place your cursor inside of string quotes in your formula and past. Alternatively, copy and pasting the below formula may preserve the line break. If not, hopefully my explanation makes enough sense to replicate on your own.
=[Item 1]@row + " " + [Color 1]@row + ",
" + [Item 2]@row + " " + [Color 2]@row + ",
" + [Item 3]@row + " " + [Color 3]@row
Hope this helps!
-
@SolutionSal THAT WORKED! Thank you. This will help me in a lot of different things I am building in smartsheet.
-
Hi @damon.tackett @SolutionSal
I hope you're well and safe!
Here's another option. ( CHAR(10) adds a new row)
=JOIN(COLLECT([Item 1]@row:[Color 3]@row, [Item 1]@row:[Color 3]@row, <>""), ", " + CHAR(10))
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.
-
@Andrée Starå Thanks for sharing! Good to know there's a simpler formulaic solution. 😃
-
Happy to help!
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.
-
CHAR(10) is one of my favourite Community discoveries 🤩
Great to see it in use!
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
@Andrée Starå neat! I am not familiar with the CHAR(10). I am going to have to play around with that!
Thank you!
-
The line break workaround is epic!!! thanks!!
-
Excellent!
Happy to help!
✅Remember! 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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 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!