Merge / Combine cell range into single cell with line break

  • I would like to merge a cell range or column into a single cell with a line break, however; I don't want to have to type out every cell number.
    I currently can do it long handed:


    Code
    1. =(Q2&CHAR(13)&Q3&CHAR(13)&Q4)


    But this is not acceptable. I have currently 200 lines in Q column and am looking to expand this number.
    Is there a way to do this?

  • Re: Merge / Combine cell range into single cell with line break


    I am not quite sure about your question as its seem not difficult...


    Please make use of 3 more column to help the issue, assuming your original data is under column A.


    Column B, Type a "Line Break" into the cell the dragging the fill handle to the bottom rows
    Column C, equal to Column A & Column B & Column C (Previous Row)


    Col A Col B Col C
    AB - =A2&B2
    BC - =C2&A3&B3
    CD - =C3&A4&B4
    DE - ....

  • Re: Merge / Combine cell range into single cell with line break


    I know you posted in Excel formulas, but just to give an alternative; this is a very simple UDF if you can accept a macro enabled workbook...


    Goes in a standard code module.


    Also, if you want line breaks in a cell, Excel prefers CHAR(10) rather than 13...


    (Even if the thread is 2 weeks old and has probably been abandoned)