Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Formula for a carriage return within a cell
Hello
i have this formula for milestones, however I would like to format it so that the "Milestone Count, Milestone Reached, Milestones to Go are all lined up to the left of the cell. I cannot change the size of the cell as there are multiple tasks in that same column.
="Milestone Count. . . . . . . " + COUNT([Notify Finance]21:[Notify Finance]74) + " Milestone Reached. . . . . . . . " + COUNTIF([Notify Finance]21:[Notify Finance]74, "Yes") + " Milestones To Go. . . . . . . . " + COUNTIF([Notify Finance]21:[Notify Finance]74, "No")
Is there a way to add a carriage return? I've tried multiple spacing but it doesnt work.
Any help will be fantastic.
Louise
Comments
-
Unfortunately, there is not a way to add carriage returns in cells.
But as a workaround, you can use the text wrap tool (on the toolbar) to achieve similar results! Make sure there is a space between your text in the formula results, then shrink the column width until the values are wrapped to multiple rows in the cell.
-
Louise,
Here's a workaround that works for me.
In a Text/Number cell in another column (could be a hidden column) [NewLine]1:
Type a comma, then hit Control-Shift Enter (Windows) or Command-Shift Enter (Mac) for a new line, then use Alt-8 (Mac) Alt +0149 (Windows) for a bullet character.The text in the cell [NewLine]1 should look like this:,•Modify your formula to reference the new cell for each of the items you're counting (Milestone Count, Milestone Reached, Milestones to Go) and add a Bullet character at the beginning of the formula.Your updated formula should look something like:="•Milestone Count: " + COUNT([Notify Finance]21:[Notify Finance]74) + [NewLine]1 + "Milestone Reached: " + COUNTIF([Notify Finance]21:[Notify Finance]74, "Yes") + [NewLine]1 + " Milestones To Go: " + COUNTIF([Notify Finance]21:[Notify Finance]74, "No")The output should look something like this:•Milestone Count: 42,•Milestone Reached: 22,•Milestones To Go: 20Ken -
Thankyou so much Ken Asher for this amazing and clever tip!
With the recent update to Smartsheet, you can also apply this same concept to Join() as the seperator!
Very Grateful,
The Code Solution
-
Ken's trick to create a carriage return in a formula will work. You can remove the extra characters that it produces by using the SUBSTITUTE function. See my community response here: https://community.smartsheet.com/discussion/feature-request-join-carriage-return-separator
