Feature Request: JOIN() carriage return separator

J. Craig Williams

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

Find more posts tagged with

Formulas

Comments

Taylor F

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

That is a thing of beauty.

Thank you Taylor.

Craig

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

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:

=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):

=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).

bdchapin

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.

Paul Newcome

A few notes in case anyone else stumbles across this thread looking for something...

1. With the new functions that have been rolled out recently, you can now use CHAR(10) to insert the carriage return separator.

=JOIN([Column Name]:[Column Name], CHAR(10))

2. To not have all of the extra separators for blank fields within a JOIN range, you can use the COLLECT function.

=COLLECT(range to collect, criteria range 1, criteria 1, criteria range 2, criteria 2, ...............)

Using this within the JOIN function, we can specify to only join fields that are text (or whatever other criteria you want to include.

=JOIN(COLLECT([Join Column]:[Join Column], [Join Column]:[Join Column], ISTEXT(@cell)), CHAR(10))