+ Reply to Thread
Results 1 to 5 of 5

Concatenate text with a line break

  1. #1
    Registered User
    Join Date
    09-11-2013
    Location
    Montreal, Qc, Canada
    MS-Off Ver
    Excel 2013
    Posts
    41

    Question Concatenate text with a line break

    Hi there,

    I have a very long table of values that I need to present in a very specific manner as as input file for a program. I need to concatenate the values but it has to include a line break, so that I can later copy (special) the content as text without format.

    I was able to do everything, except for the line break...

    So the simple question is: When you use "CONCATENATE", can you insert a line break in the resulting expression?


    Here is an example:

    My data is as follow:

    Exemple Excel 001.jpg

    And I need to get the following text: (To be effective, I should find two lines in each cell adjacent to the previous table)

    AaBa
    CbDb
    AcBc
    CdDd
    AeBe
    CfDf

    Thanks!

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Concatenate text with a line break

    The line-feed character has a code of 10, so you can do this:

    =CONCATENATE(A1,B1,CHAR(10),C1,D1,CHAR(10),A2,B2,CHAR(10) ...

    and so on. Ensure the cell has been formatted to wrap text (and you may have to adjust the row height manually, as Excel is not very good at getting that right).

    Hope this helps.

    Pete

  3. #3
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Concatenate text with a line break

    try
    =A1&B1&CHAR(13)&C1&D1
    format cell to wrap text and drag to fit
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Registered User
    Join Date
    09-11-2013
    Location
    Montreal, Qc, Canada
    MS-Off Ver
    Excel 2013
    Posts
    41

    Re: Concatenate text with a line break

    It seems to be the answer to my dilemma, however, it doesn't seem to work...

    I have only Excel 2003 at my disposal, could it be the reason?

    I wrote it as:

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    09-11-2013
    Location
    Montreal, Qc, Canada
    MS-Off Ver
    Excel 2013
    Posts
    41

    Re: Concatenate text with a line break

    I finally got it... The trick was that since I am in a french version, it had to be "CAR(10)"

    So problem solved! Thanks!

    (Note to anyone who would use it; the data appears on a single line with squares between the expressions, but pasting special without any format achieves the desired effect...)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Insert line break and format text with CONCATENATE (or &)
    By AnnieMcken in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-17-2014, 12:24 PM
  2. text line break symbol to use in a formula
    By pwermuth in forum Excel General
    Replies: 2
    Last Post: 05-17-2009, 07:46 PM
  3. Line break using Concatenate
    By kys2000 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-27-2007, 06:03 PM
  4. break line in text cell from ASP
    By Lorenzo in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-06-2006, 09:10 AM
  5. Cells.Find for text w/ line break
    By achidsey in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-14-2005, 01:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1