use concatenate function to put carrage returns in a text string

[dabblingandconfused]

How do I use concatenate to assemble a string that includes carrage returns?

 

[Biff]

Hi!

Try this:

To concatenate A1 and B1 with a "hard return" in-between:

=A1&CHAR(10)&B1

Format the cell to wrap text:

Format>Cells>Alignment>Wrap text

Biff

 

[Kishor]

hi,
Try this function.

Function AddText(CellsToAdd, TextSeperator)
y = CellsToAdd.Cells.Count
i = 1
For i = 1 To y
z = CellsToAdd(i)
k = k & TextSeperator & z
Next i
AddText = k
End Function
' =AddText(A1:E1," ") this example can add all text from range A1:E1 with
space as separator.

 

[Martha]

Biff,
Do you know if there's a limit to the number of CHAR(10)s allowed in one
formual. I have one with four that's working fine, but when I addeed the
fifth, it refuses to work. Have tried moving the fifth column of data to the
left of the formula (with the other columns), all lower case, all upper case
and with and without spaces. Can you help? Thanks much,

Martha

 

[Martha]

Biff,

Do you know if there is a limit to the number of CHAR(10)s allowed in one
formula. I have a formula with four that is working fine, but when I added
the fifth, it refused to work. Have tried re-formatting the column; all
upper case, all lower case, with and without spaces after the commas, and so
far, no luck. Can you help?

=CONCATENATE(C2,CHAR(10),TEXT(D2, "d-mmm-yyyy"),CHAR(10),G2,CHAR(10),I2," -
",J2) WORKS FINE

=CONCATENATE(C2,CHAR(10),TEXT(D2, "d-mmm-yyyy"),CHAR(10),G2,CHAR(10),I2," -
",J2,CHAR(10),K2) DOESN'T WORK