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:

Aye-Aye Books said...

Great tip for PCs,

But use CHAR(13) on a Mac

CHAR(10) will not work

Anonymous said...

Or just don't use a Mac.

Scubidu said...

You saved our afternoon...
CHAR10 was killing us

Fajar said...

thanks a lot!
very helpfull

Richard said...

Cheers, great help!

Anonymous said...

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.

Anonymous said...

Thanks a lot!

Anonymous said...

Thank you, thank you, thank you..

Unknown said...

2011 and still someone who finds this usefull, thanks!

Anonymous said...

Epic tip.

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

Thanks a bunch.

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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

Anonymous said...

Still working thanks

Anonymous said...

save my time and my life here :)
Thank you

nickpyl said...

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

Abdul Waheed said...

Thank you for sharing thhis very useful tip.

Anonymous said...

Thanks a lot.

Salai said...

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

Anonymous said...

control command return on my Macbook with Excel 2010

Arefin said...

helped me a lot.thanks

Saravanan said...

Feb 2013, still helpful. Thanks

Anonymous said...

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?

Anonymous said...

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.

bkusmin said...

July 2013, and still an awesome tip!!!

Anonymous said...

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!

Anonymous said...

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

Anonymous said...

Yup, still useful - mucho thnx

Anonymous said...

Sept. 13. Still useful :)

harrythedog said...

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

This is the tip that just keeps on giving!

harry

Anonymous said...

May '14.
Still Useful

colin said...

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

Anonymous said...

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

Anonymous said...

Sept 2014
Still helping
Thanks so much

Anonymous said...

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

Anonymous said...

thanks a lot for the post :)

Vidhoosh S said...

: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.

Anonymous said...

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 :)