combining cells for a report

Options

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

  • SolutionSal
    SolutionSal Employee
    edited 10/04/22 Answer ✓
    Options

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!