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!
