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
    Messages:
    1
    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
    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. JohnT

    concatenate, force new line

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

    coloring overy other line without doing so line by line

    Guest, Apr 1, 2005, in forum: Microsoft Excel Worksheet Functions
    Replies:
    5
    Views:
    313
    Gord Dibben
    Apr 1, 2005
  3. 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:
    276
    Guest
    Apr 20, 2006
  4. 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:
    304
    Guest
    Mar 29, 2007
  5. joeu2004

    Re: Force Distribution/ Force Ranking for Bell Curve

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

Share This Page