Line Break in a Cell Using a Formula

The basic way to add a Line Break within a cell is to press Alt+Enter (on a Mac press Ctrl+Option+Return).
After pressing Enter, the Wrap Text property will automatically be set.

You can also add a line break in a cell with a formula using the CHAR(10) function and the concatenation function "&". The example below will add a line break between "abc" and "def".

="abc"&CHAR(10)&"def"


When using a formula, you will need to manually change the format of the cell to set the Wrap Text property. Otherwise, you'll end up seeing "abc□def" where the "□" indicates the presence of the line break when the Wrap Text property is unchecked.

39 comments:

  1. Great tip for PCs,

    But use CHAR(13) on a Mac

    CHAR(10) will not work

    ReplyDelete
  2. Anonymous3:26 AM

    Or just don't use a Mac.

    ReplyDelete
  3. You saved our afternoon...
    CHAR10 was killing us

    ReplyDelete
  4. Fajar8:36 PM

    thanks a lot!
    very helpfull

    ReplyDelete
  5. Richard1:24 AM

    Cheers, great help!

    ReplyDelete
  6. Anonymous5:56 PM

    2 Years later and this post is still giving. You've just made a little fish look like an excel guru amongst those far more savvy than myself.

    ReplyDelete
  7. Anonymous8:21 AM

    Thanks a lot!

    ReplyDelete
  8. Anonymous3:57 AM

    Thank you, thank you, thank you..

    ReplyDelete
  9. 2011 and still someone who finds this usefull, thanks!

    ReplyDelete
  10. Anonymous11:51 PM

    Epic tip.

    The Wrap Text note, in particular, helped me out considerably.

    Thanks a bunch.

    ReplyDelete
  11. Anonymous8:14 AM

    I feel like I owe you my life. Thank you so very much!

    ReplyDelete
  12. Anonymous9:08 AM

    Teşekkürler (Thanks) (Danke Schön)

    ReplyDelete
  13. Anonymous9:30 AM

    Excellent post.

    I found online that CHAR(10) on Windows is the same as CHAR(13) on Mac. I do not use Mac, so I cannot verify. Very useful. Thanks!

    Dave

    ReplyDelete
  14. Anonymous5:45 AM

    It's 2012 and It saved my a lot of headaches.
    I used this little trick to fill text boxes on a range of graphs

    ReplyDelete
  15. Anonymous1:05 AM

    Still working thanks

    ReplyDelete
  16. Anonymous1:00 PM

    save my time and my life here :)
    Thank you

    ReplyDelete
  17. Hi. Is there a way to do a tab break? My book on Excel 5 says ctrl+alt+tab

    obviously not right.

    Any help would be appreciated

    ReplyDelete
  18. Thank you for sharing thhis very useful tip.

    ReplyDelete
  19. Anonymous9:33 AM

    Thanks a lot.

    ReplyDelete
  20. Its Oct 2012... and still thank you for sharing this information.

    ReplyDelete
  21. Anonymous8:25 PM

    control command return on my Macbook with Excel 2010

    ReplyDelete
  22. Arefin9:26 PM

    helped me a lot.thanks

    ReplyDelete
  23. Saravanan12:19 PM

    Feb 2013, still helpful. Thanks

    ReplyDelete
  24. Anonymous12:09 PM

    I want to put a code into my txt-document which Excel will understand as a line break. In html-code this would be but what code can be used from txt to Excel?

    ReplyDelete
  25. Anonymous12:28 AM

    I have a cell of ten lines and this is tedious to put as a code between each line. The alt+enter would work more easily. Is there some code I could apply for a whole paragraph to put a space in between each line or would I have to put the single line break in between each line individually.

    ReplyDelete
  26. bkusmin9:29 PM

    July 2013, and still an awesome tip!!!

    ReplyDelete
  27. Anonymous8:58 AM

    Works perfectly when using an Excel sheet as the data source for a mail merge document. I'm using the CONCATENATE command with IF statements to produce the statement. The line breaks print exactly as requested.
    Thank you!

    ReplyDelete
  28. Anonymous10:56 PM

    Use CHAR(13) on a mac... thank you a thousand times, saved the day!

    ReplyDelete
  29. Anonymous3:48 AM

    Yup, still useful - mucho thnx

    ReplyDelete
  30. Anonymous5:18 AM

    Sept. 13. Still useful :)

    ReplyDelete
  31. Char(10) and Word Wrap.
    Works fine in Excel 2010

    This is the tip that just keeps on giving!

    harry

    ReplyDelete
  32. Anonymous4:28 AM

    May '14.
    Still Useful

    ReplyDelete
  33. It took me a little Google-Fu to find this, but after wading through all the alt+enter recommendations, this was muy helpful. Thanks!

    ReplyDelete
  34. Anonymous7:35 AM

    Thank you so much for the tip!! you just saved my day :)

    ReplyDelete
  35. Anonymous8:54 AM

    Sept 2014
    Still helping
    Thanks so much

    ReplyDelete
  36. Anonymous8:56 AM

    Sept 2014
    I still found this a helpful tip
    with excel 2010

    ReplyDelete
  37. Anonymous12:04 PM

    thanks a lot for the post :)

    ReplyDelete
  38. Vidhoosh S5:01 AM

    :D I just scrolled down to say Thank You..

    After reading through so many dated notes, wow, blog posted in 2009 helped me in 2015. Thanks a ton.

    ReplyDelete
  39. Anonymous11:16 AM

    Thank you for this, I was having a hard time trying to figure out how to do this. It is September 2016 and still works :)

    ReplyDelete