Results 1 to 7 of 7

CONCATENATE with a Carriage Return? Can you?

This is a discussion on CONCATENATE with a Carriage Return? Can you? within the Excel Questions forums, part of the Question Forums category; Hi All: I did a quick search but did not fine what I was looking for (too many results). I ...

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Sudbury, Ontario Canada
    Posts
    1,360

    Default CONCATENATE with a Carriage Return? Can you?

    Hi All:

    I did a quick search but did not fine what I was looking for (too many results).

    I need to combine some data that is currently in 2 columns (G and H). The problem I am having is trying to get a carriage return in there so that the info from Column H goes below the info from Column G. I placed a carriage return in J and tried this:

    =CONCATENATE(G7 & J7 & H7)

    The info went together but all on one line. Is what I am asking for possible and if so how? These are all address listing like this:

    G7 = "4000 Sheppard Avenue East"
    H7 = "Scarborough, Ontario M1S 4X4

    I need my result shown as:

    4000 Sheppard Avenue East
    Scarborough, Ontario
    M1S 4X4

    Any suggestions?

    THANKS,
    Mark

  2. #2
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    62,237

    Default Re: CONCATENATE with a Carriage Return? Can you?

    Try this

    =G6&CHAR(10)&H6

    format the result sheet to wrap text.

  3. #3
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    31,914

    Default Re: CONCATENATE with a Carriage Return? Can you?

    Tip 1. If you're going to use the &, you might as well drop the CONCATENATE...

    it's lke saying SUM(SUM(A1,B1))
    =CONCATENATE(G7 & J7 & H7)
    Same As
    =A7 & J7 & H7

    2.
    use CHAR(10) for a carriage return
    =A7 & CHAR(10) & J7 & CHAR(10) & H7

    You'll have to turn on Word Wrap
    Format - Cells - Alignment
    Wrap Text
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Sudbury, Ontario Canada
    Posts
    1,360

    Default Re: CONCATENATE with a Carriage Return? Can you?

    THANKS to BOTH of you for your help. I did not know what to use for a Carriage Return. You have both indicated CHAR(10) and I was just wondering if this and other codes are listed in Excel or is this just something that IT type people know. If it is listed maybe there are other things of interest there as well..?

    Regardless, you guys have me up and running.

    Have a GREAT day and THANKS Again...
    Mark

  5. #5
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    62,237

    Default Re: CONCATENATE with a Carriage Return? Can you?

    There's a list of character codes here http://www.lookuptables.com/

  6. #6
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    31,914

    Default Re: CONCATENATE with a Carriage Return? Can you?

    You can easily get a displayed list of them all by doing this on an available column

    =CHAR(ROW())
    copy down 255 rows.
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    Sudbury, Ontario Canada
    Posts
    1,360

    Default Re: CONCATENATE with a Carriage Return? Can you?

    THANKS AGAIN to BOTH of you.

    I will check out the Posted List and try and create one myself

    Take Care,
    Mark

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