Formula for accumulating text data in a cell with carriage return

[Sinisha]

Hi!

I hope someone can shed some light on the following problem:

- I have only 4 cells in the same row (A,B,C,D)
- Cells A, B, C contain predictable text data. Cell A is either empty or contains text "plank", cell B is either empty or contains text "squats", cell C is either empty or contains text "stretch"
- In one row all 4 cells may be filled out, none or just some
- What I would like to have in the cell D is a summary of the A,B,C cells in a way that text from these cells is copied to cell D one below the other
- Example: Let's say that cells A,B,C are all filled out. What I would need in cell D, in that case, is the text:
plank
squats
stretch

The carriage return is a must because I'm using this sheet later on for importing data into DB and this is the format DB will recognize.

Usually, I can handle excel formulas but this one is simply beyond me.

Help is much appreciated!

 

[steve the fish]

Try this:

=TEXTJOIN(CHAR(10),TRUE,A1:C1)

or if you dont have the function textjoin you could try:

=SUBSTITUTE(TRIM(A1&" "&B1&" "&C1)," ",CHAR(10))

 

[CA_Punit]

Make sure the Wrap Text Function is used in the cell to enable Line Breaks

 

[Sinisha]

Spot on! TEXTJOIN does the job perfectly and Wrap Text tip was a great pointer.

Thanks so much to both of you!