Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Concatenate, but onto new line

This is a discussion on Concatenate, but onto new line within the Excel Questions forums, part of the Question Forums category; I want to bring together three columns of text into 1 column. For example: A = CommentA B = CommentB ...

  1. #1
    New Member
    Join Date
    Sep 2002
    Posts
    4

    Default

    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

  2. #2
    Board Regular s-o-s's Avatar
    Join Date
    Apr 2002
    Location
    Kissimmee, Florida
    Posts
    384

    Default

    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



    Hope This Helps.
    Sean.
    Digest of Homes

    WinXP, XL XP

  3. #3
    Board Regular Barry Katcher's Avatar
    Join Date
    Feb 2002
    Location
    Dog Beach, Florida. Yeaahh!
    Posts
    4,055

    Default

    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.
    Barry-

    Photo Restoration/Enhancement

    http://www.smiledogproductions.com
    click below for detour


  4. #4
    Board Regular
    Join Date
    Jul 2002
    Location
    Milton Keynes, England
    Posts
    10,929

    Default

    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.

  5. #5
    New Member
    Join Date
    Sep 2002
    Posts
    4

    Default

    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

  6. #6
    Board Regular Barry Katcher's Avatar
    Join Date
    Feb 2002
    Location
    Dog Beach, Florida. Yeaahh!
    Posts
    4,055

    Default

    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.
    Barry-

    Photo Restoration/Enhancement

    http://www.smiledogproductions.com
    click below for detour


  7. #7
    Board Regular s-o-s's Avatar
    Join Date
    Apr 2002
    Location
    Kissimmee, Florida
    Posts
    384

    Default

    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.


    Hope This Helps.
    Sean.
    Digest of Homes

    WinXP, XL XP

  8. #8
    Board Regular
    Join Date
    Jul 2002
    Location
    Milton Keynes, England
    Posts
    10,929

    Default

    Richard,

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

    Remember to format alignment as Wrap Text switched on.

    Glenn.

  9. #9
    Board Regular
    Join Date
    Jan 2008
    Posts
    711

    Default Re: Concatenate, but onto new line

    Is there a way to add a bullet infront of each line?

    http://www.cpearson.com/excel/chars.htm

    Found it.
    Last edited by PCRIDE; Jun 7th, 2011 at 11:33 PM.

  10. #10
    Board Regular
    Join Date
    Jul 2002
    Location
    Milton Keynes, England
    Posts
    10,929

    Default Re: Concatenate, but onto new line

    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.
    Cheers, Glenn.

    Beauty is in the eye of the beer-holder.

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com