how do you force a new line in a formula in Excel?

Discussion in 'Microsoft Excel Worksheet Functions' started by JeffRI, Mar 24, 2008.

  1. JeffRI

    JeffRI Guest

    I'm trying to use the CONCATENATE function but I also want to force a
    'return' in the function/formula as if entering data into a cell and using
    ALT+ENTER to force a new line. Can this be done?
     
    JeffRI, Mar 24, 2008
    #1
    1. Advertisements

  2. JeffRI

    Tom Hutchins Guest

    You can use CHAR(10) in your Concatenate function, like this:
    =CONCATENATE("First line",CHAR(10),"Second line")

    You also need to format the cell to allow text wrapping, or the linefeed
    character will appear as a little square box.

    Hope this helps,

    Hutch

    "JeffRI" wrote:

    > I'm trying to use the CONCATENATE function but I also want to force a
    > 'return' in the function/formula as if entering data into a cell and using
    > ALT+ENTER to force a new line. Can this be done?
     
    Tom Hutchins, Mar 24, 2008
    #2
    1. Advertisements

  3. JeffRI

    JeffRI Guest

    This was awesome!! Thx for the info! That's exactly what I was looking for.

    "Tom Hutchins" wrote:

    > You can use CHAR(10) in your Concatenate function, like this:
    > =CONCATENATE("First line",CHAR(10),"Second line")
    >
    > You also need to format the cell to allow text wrapping, or the linefeed
    > character will appear as a little square box.
    >
    > Hope this helps,
    >
    > Hutch
    >
    > "JeffRI" wrote:
    >
    > > I'm trying to use the CONCATENATE function but I also want to force a
    > > 'return' in the function/formula as if entering data into a cell and using
    > > ALT+ENTER to force a new line. Can this be done?
     
    JeffRI, Mar 24, 2008
    #3
  4. JeffRI

    Tyro Guest

    You can also use ="Line1" & CHAR(10) & "Line2"
    CONCATENATE is such a long way of saying &.
    You must format the cell to wrap text.

    Tyro

    "JeffRI" <> wrote in message
    news:...
    > I'm trying to use the CONCATENATE function but I also want to force a
    > 'return' in the function/formula as if entering data into a cell and using
    > ALT+ENTER to force a new line. Can this be done?
     
    Tyro, Mar 24, 2008
    #4
  5. JeffRI

    gamut

    Joined:
    Oct 2, 2012
    Likes Received:
    0
    If you need to do this on Mac, there is a silly way to achieve it. Basically, you need to put an actual newline inside quotes. Like so:
    =A1 & "
    " & B2

    To put a newline in there, press Ctrl-Alt-Enter
     
    gamut, Oct 2, 2012
    #5
  6. JeffRI

    Andy Spry

    Joined:
    Sep 9, 2015
    Likes Received:
    0
    I have a similar challenge, but want to transform the appearance of each line in a multi-line cell and preserve the line ends. Basically I want cell content like this:
    NRTMS-URS-REQ-001
    NRTMS-URS-REQ-002
    NRTMS-URS-REQ-004
    NRTMS-URS-REQ-2202
    to look like this:
    [NRTMS-URS-REQ-001]
    [NRTMS-URS-REQ-002]
    [NRTMS-URS-REQ-004]
    [NRTMS-URS-REQ-2202]

    Any ideas?
     
    Andy Spry, Sep 9, 2015
    #6
  7. JeffRI

    Andy Spry

    Joined:
    Sep 9, 2015
    Likes Received:
    0
    Worked it out in the end. 3 steps. 1) Use Replace to add the leading [. 2) Use Substitute to replace each line break with a line break preceded by a trailing ]. 3) Use Concatenate to append a trailing ] to final line of text in each cell.
     
    Andy Spry, Sep 11, 2015
    #7
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. RDI

    Cell Formatted w/ Word Wrap--Force Text to Next line?

    RDI, Dec 4, 2003, in forum: Microsoft Excel Worksheet Functions
    Replies:
    0
    Views:
    238
  2. RDI

    Cell Formatted w/ Word Wrap--Force Text to Next line?

    RDI, Dec 4, 2003, in forum: Microsoft Excel Worksheet Functions
    Replies:
    2
    Views:
    1,089
  3. RDI

    Cell Formatted w/ Word Wrap--Force Text to Next line?

    RDI, Dec 4, 2003, in forum: Microsoft Excel Worksheet Functions
    Replies:
    0
    Views:
    239
  4. JohnT

    concatenate, force new line

    JohnT, Mar 6, 2005, in forum: Microsoft Excel Worksheet Functions
    Replies:
    2
    Views:
    881
    Guest
    Mar 6, 2005
  5. Guest

    coloring overy other line without doing so line by line

    Guest, Apr 1, 2005, in forum: Microsoft Excel Worksheet Functions
    Replies:
    5
    Views:
    415
    Gord Dibben
    Apr 1, 2005
  6. Guest

    How do you add a blank line automatically after the Subtotal line

    Guest, Apr 20, 2006, in forum: Microsoft Excel Worksheet Functions
    Replies:
    0
    Views:
    354
    Guest
    Apr 20, 2006
  7. Guest

    Can you use a formula to edit another formula in excel?

    Guest, Mar 28, 2007, in forum: Microsoft Excel Worksheet Functions
    Replies:
    4
    Views:
    389
    Guest
    Mar 29, 2007
  8. joeu2004

    Re: Force Distribution/ Force Ranking for Bell Curve

    joeu2004, Jun 21, 2013, in forum: Microsoft Excel Worksheet Functions
    Replies:
    0
    Views:
    659
    joeu2004
    Jun 21, 2013
Loading...