Concatenate, but onto new line

rj183600

New Member
Joined
Sep 22, 2002
Messages
4
I want to bring together three columns of text into 1 column. For example:
A = CommentA
B = CommentB
C = CommentC

If you concatenate (=A&B&C) you get CommentACommentBCommentC. I know you can put in characters and spaces using "" inbetween the & (e.g =A&","&B&","&C), but is there a way to get excel to move the next comment onto a new line within the same cell so it looks like:
CommentA
CommentB
CommentC
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
On 2002-11-19 08:01, rj183600 wrote:
I want to bring together three columns of text into 1 column. For example:
A = CommentA
B = CommentB
C = CommentC

If you concatenate (=A&B&C) you get CommentACommentBCommentC. I know you can put in characters and spaces using "" inbetween the & (e.g =A&","&B&","&C), but is there a way to get excel to move the next comment onto a new line within the same cell so it looks like:
CommentA
CommentB
CommentC

=cell1 & " Alt + Enter " & cell2 & " Alt + Enter " & Cell3

will do it make sure cell wrap is checked in the cell format

:)
 
Upvote 0
You can try Wrap Text on the cell you concatenate into. Little tricky to get it right, column width wise, especially if the three comments are of different length.
 
Upvote 0
Try using the function CHAR to generate a newline character in your formula. It should look something like this ...
=A1&CHAR(10)&B1&CHAR(10)&C1

You will probably need to format the cell so that alignment has text wrap activated.

Glenn.
 
Upvote 0
Sean - can't get this to work, just comes out with CellYAlt+EnterCellZ

Barry - thanks, but all comments are different lengths

Glenn - looks good, but can't find/don't know my new line character. Any ideas how 2 do this?

Cheers

Richard
 
Upvote 0
Try this: In between A1 & B1 and in between B1 and C1, place as many spaces as your longest text.

=A1&" "&B1&" "&C1

Then, widen your column appropriately.
 
Upvote 0
Sean - can't get this to work, just comes out with CellYAlt+EnterCellZ

You do not type in the Alt and Enter you hold the Alt Key and press Enter between the quotes which inserts the newline character.
 
Upvote 0
Richard,

CHAR(10) is the newline character, as in my example.

Remember to format alignment as Wrap Text switched on.

Glenn.
 
Upvote 0
Wow, that thread takes me back ... more than 8 years since I last posted to it. Glad that you found out how to do it, and thanks for putting the link to Chip's useful info too.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top