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.

Feature Request: JOIN() carriage return separator

J. Craig Williams
J. Craig Williams ✭✭✭✭✭✭
edited 12/09/19 in Archived 2016 Posts

I would like to JOIN() to allow the carriage return as a separator.

 

This would allow me to transpose the data.

 

JOIN([Column1]23:[Column12]23,"\n") would take the row and give me a cell with the contents transposed vertically.

 

JOIN([Column1]1:[Column1]22," ") would take the column and give me a cell with the contents transposed horizontally.

 

Integration to tools like Evernote or Slack would allow the notes to be visually easier to read and comprehend.

 

Thanks.

 

Craig

 

 

Tags:

Comments

  • Taylor F
    Taylor F Employee Admin

    Hi Craig,

     

    Although we don't have the ability to code in a carriage return in the JOIN function, we do have a workaround. 

     

    What you will need to do is create a new Text/Number column called "Carriage Return" (or something similar) and in the first cell place a hyphen, a carriage return, and a hyphen. You can then hide this column on the sheet. 

     

    Then in the JOIN function, refer to this first cell and place the cell reference in the SUBSTITUTE function and replace the hypens with nothing.

     

    =JOIN([Task Name]1:[Task Name]5, SUBSTITUTE([Carriage Return]$1, "-", ""))

     

    This will JOIN Task Name 1 through Task Name 5 with a carriage return. Be sure to enable Wrap on the cell that contains the JOIN formula in order to view the values in a list. 

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    That is a thing of beauty.

    Thank you Taylor.

     

    Craig

     

     

  • Lee Joramo
    Lee Joramo ✭✭✭✭✭✭

    A slight improvement on this is to use the new Summary Sheets so that you don't need to create a column. Add a field "Carriage Return" to your Summary Sheet with the string containing the return an use the forumula:

    =JOIN([Task Name]1:[Task Name]5, SUBSTITUTE([Carriage Return]#, "-", ""))

  • weswillis
    weswillis ✭✭
    edited 02/07/19

    I realize this is a pretty old thread, but I'm going to give it a shot anyway... So that solution worked for my situation but it does have a bit of a side-effect. I'm joining cells (and doing some IF statements I figured IF statements out) that may have information and may not, but within the formula I'm using the substitution solution above. On the blank cells, obviously nothing shows when everything gets rendered, but the carriage returns are there still. This leaves you with a block of text that has large areas of white space between the items you are joining. 

    So, is there a method of employing this solution where the carriage return could be ignored on cells without content?

    Example of the IF statement:

    no

    =IF([A1A]1 = 0, "⚠ Section A1 (A) - NOT SUBMITTED", "") + SUBSTITUTE(Carriage$1, "-", "") + IF([A1B]1 = 0, "⚠ Section A1 (B) - NOT SUBMITTED", "") + SUBSTITUTE(Carriage$1, "-", "") + IF([A2]1 = 0, "⚠ Section A2 - NOT SUBMITTED", "") + SUBSTITUTE(Carriage$1, "-", "")

    UPDATE: I just needed 5 minutes to stop thinking about it I guess. Solved my own IF statement problem (using the example above, I did the following):

    yes

    =IF([A1A]1 = 0, "⚠ Section A1 (A) - NOT SUBMITTED" + SUBSTITUTE(Carriage$1, "-", ""), "") + IF([A1B]1 = 0, "⚠ Section A1 (B) - NOT SUBMITTED"+ SUBSTITUTE(Carriage$1, "-", ""), "") + IF([A2]1 = 0, "⚠ Section A2 - NOT SUBMITTED"+ SUBSTITUTE(Carriage$1, "-", ""), "")

    Example of the JOIN: (Still need to know if it's possible in a JOIN argument)

    =JOIN(CHILDREN(), SUBSTITUTE(Carriage$1, "-", ""))

     

    Both ways render the carriage return even for blank cells (I guess because the SUBSTITUTE isn't being controlled by any conditional statements). 

    It seems that the JOIN method may be trickier than the IF. It seems like there should be a way to build the SUBSTITUTE into the IF statement, but I've been experimenting with it for hours now and I'm too aggravated to continue on my own (Ha).

    Any suggestions?

    This isn't a deal breaker, it just looks like garbage when it renders (Including the screenshot of how it renders).

    Capture2719.PNG

  •  

    weswillis,

    I had a case that was somewhat similar and I was able to use the Collect() function to filter out the junk that I didn't want to join into the text string. 

    Collect() basically uses the logic from a countifs() and in stead of counting the values that match it returns the array of values that match. 

This discussion has been closed.