combining cells for a report

damon.tackett

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.

SolutionSal

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

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!

damon.tackett

@SolutionSal THAT WORKED! Thank you. This will help me in a lot of different things I am building in smartsheet.

Andrée Starå

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!

SolutionSal

@Andrée Starå Thanks for sharing! Good to know there's a simpler formulaic solution. 😃

Andrée Starå

@SolutionSal

Happy to help!

Genevieve P.

CHAR(10) is one of my favourite Community discoveries 🤩

Great to see it in use!

damon.tackett

@Andrée Starå neat! I am not familiar with the CHAR(10). I am going to have to play around with that!

Thank you!

Hobo

The line break workaround is epic!!! thanks!!

Andrée Starå

@Hobo

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!