MS-Excel: How to Use Line Break in Formula

Real power of Excel, the popular spreadsheet software from Microsoft Corporation, can be harnessed when you use functions and formulas. Using formulas, you can virtually do anything you want in a Excel spreadsheet. The other day my colleague came to me and asked how to insert a line break in an MS Excel formula. Such a scenario rises especially when you’re using formula for manipulating strings by joining or breaking them.

When my colleague asked me about this requirement –I first tried the “programmer’s line break”, i.e. \n … but it did not work and Excel reported an error in the formula. However, after a bit of research, finally I fixed it and here is the solution.

Line break in Excel Formula

You need to use CHAR function to insert line break character at the appropriate spot in your Excel formula. For example:

=A1&CHAR(10)&A2

The above formula will combine content of cells A1 and A2 but will insert a line break in between. For example,

A1 = “I love”

A2 = “MS Excel”

Now if you place the above formula in cell A3, the result will be:

A3 = “I love
MS Excel”

Please not that 10 is the ASCII code for line break and & is the concatenation operator used in Excel. So, the above formula append content of cell A1 and CHAR(10) and content of cell A2.

IMPORTANT NOTE: After you have used the above formula in a cell –you’ll need to format that cell with Word Wrap.

If you will not use Word Wrap, you will not see the line break doing its job. Instead you’ll either not see anything or may see a square symbol in place of line break. Excel Word Wrap interprets and displays line break properly.

On TechWelkin we publish a lot of very useful Excel tips and tutorials. I hope the tip given in this article was useful for you. Do let me know if you have any questions. Thank you for using TechWelkin!