How to Insert Line Break in Excel Cell Using Formula
When creating charts and diagrams in Excel, there is often a need to insert a line break in the text of a cell and more. For example, to achieve a better visual effect for readability of data labels on charts and diagrams.
Formula for Line Break in Excel Cells
Let's consider the example below with a histogram created in Excel. The labels of the X-axis contain the names of sellers and sales data. Such an example of labeling data on a histogram is always useful when text needs to be compactly arranged so as not to overshadow elements of the histogram.
The secret of this solution is quite simple and elementary. You just need to add the CHAR function to the desired place in the formula, which generates values in cells for labeling histogram data.
Of course, you can insert a line break in a cell at any place using the ALT+Enter keyboard shortcut. But if there are dozens of such cells, it's better to automate this process with a simple formula as shown above.
How the CHAR Function Works for Line Breaks in a Cell
MS Excel actively uses codes from the ASCII character set. It contains all the codes for characters displayed on the screen in MS Windows operating systems of any version. The ASCII character set table contains 255 codes, each numbered from 1 to 255. For example, the uppercase letter A has the code 65. The number 9 is assigned the code 57. The space character has the code 32, and the line break code is number 10. This table also contains non-printable characters from the keyboard.
Knowing the corresponding codes from the ASCII table and using the CHAR function, we can insert any character into an Excel formula. In the example above, a line break character is inserted, which is connected on both sides by the & operator between the values of cells A2 and C2.
If the "Wrap Text" mode is not enabled in the cell, the presence of a line break character will be unnoticed:
DownloadBut in histograms, charts, and diagrams, line break characters inserted using formulas or entered from the keyboard (ALT+Enter) will be displayed naturally. That is, breaking one text string into two or more with line breaks.
