Formula for a carriage return within a cell

Louise O'Day

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

Bobby Andres

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.

 

 

Ken Asher

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: 20

 

Ken

The Code Solution

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

Taylor F

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