CONCATENATE with a line break

[XL_F_Lancer]

Good Evening -

I have four cells, FirstName1, LastName1, FirstName2, LastName2

I would like to concatenate them into one cell, but with a line break between Name1 and Name2. It would look like:

John Smith
Susie Smith

in the same cell

I have the CONCATENATE part all figured out, but I don't know how to tell Excel to put it on the next line (not next row)

Any ideas?

Thanks!!

 

[xld]

=A1&" "&B1&CHAR(10)&C1&" "&D1

you will still need wrap-text.

 

[Keith412]

Can you just format the concatenated cell to wrap the text?

 

[Legacy 51064]

Concatenate(a1,", ",a2,char(10),a3,", ",a4)

 

[Gerald Higgins]

Can you just format the concatenated cell to wrap the text?

I think this might have undesired effects with extremely long and extremely short text strings.

For example, if you set up the wrapping to work for SUSIE SMITH, it might not work correctly for both JO COX and SUSANNA-MARIE SMITHERS-TRUMPINGTON

 

[XL_F_Lancer]

the CHAR(10) is perfect... as mentioned above, with varying text lengths the wrap isn't adequate for the needs.

Thanks so much!!

 

[giacle]

=A1&" "&B1&CHAR(10)&C1&" "&D1

you will still need wrap-text.

I'm using MS Excel 2010 and I cannot insert a line feed with string concatenation. Using the suggested sintax I obtain an error as #NAME?. Any help on this matter? Thank to anibody in advance

 

[Korbi]

Hey all,
I need to marge multiple cells in one cell separated with a comma.
I did this:
=C3&","&D3&","&E3&","&F3&","&G3&","&H3&","&I3&","&J3&","&H3&","&K3&","&L3&","&M3&","&N3&","&O3&","&P3&","&Q3
this is the result
[TABLE="class: cms_table, width: 797"]
<tbody>[TR]
[TD="width: 797"]Astab-/Astausbruch,,Astungswunden/-fäule,,,,Höhlungen,,,,,,,Totholz,, (there are blank cells so got those ",,,,,")[/TD]
[/TR]
</tbody>[/TABLE]


how can i skip the blank cells?
to get something like this:
[TABLE="class: cms_table, width: 797"]
<tbody>[TR]
[TD="width: 797"]Astab-/Astausbruch,Astungswunden/-fäule,Höhlungen,Totholz,

Thanks [/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

Give this a try...

=MID(IF(C3="","",","&C3)&IF(D3="","",","&D3)&IF(E3="","",","&E3)&IF(F3="","",","&F3)&IF(G3="","",","&G3)&IF(H3="","",","&H3)&IF(I3="","",","&I3)&IF(J3="","",","&J3)&IF(K3="","",","&K3)&IF(L3="","",","&L3)&IF(M3="","",","&M3)&IF(N3="","",","&N3)&IF(O3="","",","&O3)&IF(P3="","",","&P3)&IF(Q3="","",","&Q3),2,999)

This can be made a little more efficient by reducing the 999 to a lower number that is guaranteed to be more than the total number of characters in the final answer.