Excel – Insert a Line Break in a Cell

When entering text into a cell, sometimes we’d like to insert a line break for better formatting. A line break is like when you’re in Word and you press Enter to create a new line. This can also be called a carriage return. We’ll cover two ways to do this.

  • Line breaks in plain text
  • Line breaks in cell formulas

 

 

 

 

One thing is certain, you shouldn’t use two cells for creating the illusion of line breaks.

Excel line break

Line Breaks in Plain Text

This one is easy. While I’m editing the text of a cell, I can simply press Alt+Enter (or Command+Option+Enter for Mac) to add a line break as shown below.

Excel line break example for Windows and Mac

 NOTE: This will automatically set Wrap Text ON for cell A1:

Excel line break turns wrap text ON

Let’s take a look at what happens when I turn Wrap Text OFF:

Excel line break when Wrap Text is OFF

Notice that in the formula bar the break is kept there, but it is one single line in cell A1. This is because Excel needs to format the break and Wrap Text is the way to do it. The formula bar is immune to show any formatting, but it will show you the data you have (in this case, some text, then a line break and some more text after). It’s just another way to show you that you have a break in the cell, but it’s not formatted to show it that way.

Line Breaks in Cell Formulas

To enter a line break in a cell formula, reference the text and concatenate it with the ampersand (or you can use the CONCATENATE() function) along with the function CHAR(10) to insert the break.  The CHAR() function takes in an integer and will show a character based on that integer.  Please see Microsoft’s page on the CHAR() function for more info.  The 10 in this case refers to the character “Line Feed.”

I put text into A1 and A2 and in B1 I want to put them together and add a break in between them.  The formula I use is:

=A1&CHAR(10)&A2

Excel line break in a cell formula

Notice this time that Excel does not automatically turn on Wrap Text when we enter the line break as a formula.  You have to manually turn it on to get the formatting you want.  Here is the formula with Wrap Text turned on.

Excel line break in formula with formatting

Want to see more content like this?

Questions?  Comments?  Please post a comment below!  I’d love to hear anything you would like me to write about or if you need me to clarify anything.

 

Learn Excel Dashboard Course Advanced Web Hosting for only $4/month
JosephExcel – Insert a Line Break in a Cell

Comments 27

    1. Post
      Author
      Joseph

      Hi Sebastian, I’m glad this helped you out! Please let me know if you have anything else you’d like me to discuss and I would be glad to write about it.

  1. Eugeny Sattler

    Here is the story continued. Sometimes you will need to eliminate all line breaks in a range. And you will find this is not a simple task if there are many of them. Here is a solution I came accross on the web.
    1. Non VBA method
    2. VBA method would be to just repeat actions listed by the Excel Addict with macro recorder turned on AFTER the range is selected (in order to make recorded macro universal).

    1. Post
      Author
      Joseph

      Hi Eugeny,

      It didn’t occur to me that while I show people how to put line breaks in, I never showed them how to take them out! :) Thanks for sharing!

  2. pk

    thanks, the ‘wrap text’ option worked for me when my referencing another cell that
    contains a ‘line break’

    cheers

  3. Adrian Brown

    Thanks for the line break solution. I have been struggling with this for years. Never occurred to me to use the line feed character, although I am old enough to have used it in the olden days when coding for dot matrix printers…

    1. Post
      Author
  4. Josefina

    My partner and I stumbled over here coming from a different web page and thought I might check things out.
    I like what I see so i am just following you.

    Look forward to finding out about your web page again.

  5. Carol

    Hi Joseph, I like the style that you use to share your knowledge, it is nice to find something funny while you are learning :-) I love the funny pictures that you include in your posts.

    I need a little help with a task that I was given at my job. I have a spreadsheet with lots of records (name of institutions), but whoever created this file didn’t keep the case consistently. Some of the records are all in uppercase and some of them are in lowercase. I need to make them look correct now: first letter of each word should be uppercase. Is there anyway I can fix this without having to use code?

    Thanks!

    Carol

    1. Post
      Author
    1. Post
      Author
  6. Joe

    I’m using CHAR(10) in a formula like this:

    =IF(B4″”,M$1&CHAR(10),”")

    But when I click away from the cell, the formula is displayed and not the desired result. (Displayed as if it were text.) This happens for every formula using CHAR(10).

    I feel like I’m missing something really obvious. Help?

    1. Post
      Author
      Joseph

      Hi Joe,

      I assume your formula is:
      =IF(B4="",M$1&CHAR(10),"")

      If so, have you tried turning on Wrap Text in the cell that this formula is in? This is what I see:

      It puts in an extra line at the end. Is this not working for you?

  7. Hien Tran

    Hello Joseph,

    I have a cell using Wrap Text with contents like
    A
    B
    C
    Could you please help me to split that cell into three rows using formula (without VBA)?

    Thanks in advance.

    1. Post
      Author
      Joseph

      Hi Hien,

      You would have to use 3 formulas, one for each cell to capture A, B, and C:

      =MID($A$1,1,1) < -- to get "A"
      =MID($A$1,3,1) < -- to get "B" (notice that I start on the 3rd character. that's because the 2nd character is the carriage return
      =MID($A$1,5,1) < -- to get "C"

      If you don't know you'll always have one character and you're just looking to split the text based on the carriage return, then say you had a cell (B1) that had this:
      this is some text
      this is some more
      here's some more

      You could use this formula in B2:
      =MID($B$1,1,FIND(CHAR(10),$B$1))

      Then this formula in B3:
      =MID($B$1,SUM(LEN($B$2:$B2)),FIND(CHAR(10),$B$1,SUM(LEN($B$2:$B2))))

      Be sure to enter that as an array formula. Then drag that formula down.

  8. samir

    Hello Joseph,
    please, I also need your help!
    I need line breaks (before RECODE; before END IF; before EXECUTE) in this formula because I want to copy this to SPSS and there it only works if everything is in a new line.
    How can I make this work?

    DO IF (FA1 <= 3). RECODE @1 (1=1) INTO @1_Int. END IF. EXECUTE.

    1. Post
      Author
      Joseph

      Hi Samir,

      I don’t know what SPSS is, and from a quick Google search it looks like an IBM product. However, what I think you’re looking for is this:

  9. James

    Great info. But I’m trying to place a line feed within the custom format of a date.
    cell A1 has value 24/12/2014
    custom format for cell is ddd mmm dd which displays Wed Dec 24
    due to column size restrictions, wrap text doesn’t wrap like
    Wed
    Dec 24

    I tried custom format of ddd “&CHAR(10)&” mmm dd
    I get
    Wed &CHAR(10)& Dec 24

    any ideas ?

Leave a Reply

Your email address will not be published. Required fields are marked *


9 + = ten