Abrar-ur-Rahman Niloy
Line breaks are important to improve visuals in cells. It can also be used to store more than one piece of data if needed in a single cell. Whatever your cause might be, here I will help you with how to do a line break in Excel.
Sometimes we need to store multiple lines of information in a single cell. For example, a person’s details. So that we can understand the data better, again if there are too many words in a single cell, it’s better to start with new lines or paragraphs. In such cases, you must know how to add line breaks.
The above overview image shows 3 formulas for inserting a line break in Excel. Follow along to know more in detail.
Download Practice Workbook
You can download the workbook with all the sheets and examples used for this demonstration below. Download and give it a try.
How to Do a Line Break in Excel: 3 Easy Ways
Here we’ll cover the keyboard shortcut method, the Find and Replace command, and formulas to do a line break in Excel.
1. How to Do a Line Break in Excel Through Keyboard Shortcut
Double-click on the desired cell and place your cursor where you want the line break. For Windows users, press Alt and Enter simultaneously. If you are using Mac, press Control + Option + Enter.
After repeating the same process where the line breaks are needed, you’ll get the output as shown in the above image.
Note:
If you fail to see all the contents in a cell clearly, adjust the row heights manually. Or you can double-click the row borders (observe the cursor shape in the image) to adjust automatically.
Read More: [Fixed!] Line Break in Cell Not Working in Excel
2. Use Find and Replace Command to Add a Line Break After a Specific Character
You can also use the Find and Replace command in Excel to do a line break. With this tool, you can replace a character or a set of characters with other ones. To break lines we can suitably use a particular character to replace it with a line break.
Select C5:C11 and press Ctrl and H keys together.
The Find and Replace dialog box pops out. Insert:
Find what >> A space
Replace with >> Press Ctrl + J
Here, Ctrl + J is the shortcut for character 10, a line break, in the ASCII code.
Lastly, press Replace All.
Hence, the cells with desired line breaks will appear.
Read More: How to Remove Line Breaks in Excel (5 Ways)
Similar Readings
- How to Put Multiple Lines in Excel Cell (2 Easy Ways)
- VBA to Generate Multiple Lines in Email Body in Excel (2 Methods)
- How to Add a Line in Excel Cell (5 Easy Methods)
3. Insert Line Break with CHAR Function in Excel
The CHAR function takes a numerical argument and returns the character which the number represents. CHAR(10) indicates a line break in Excel formulas. We’ll combine it with other functions to add a line break in Excel.
3.1 Use Ampersand Sign to Start New Line
To combine CHAR(10) with the ampersand (&) sign, use the formula in cell E5:
=B5 & CHAR(10) & C5 & CHAR(10) & D5Apply AutoFill and you’ll get the cells with line breaks.
Read More: How to Replace Line Break with Comma in Excel (3 ways)
3.2 Insert CONCAT Function to Do a Line Break
The CONCAT function takes several arguments and concatenates all the values in them.
In cell E5, insert the formula:
=CONCAT(B5, CHAR(10), C5, CHAR(10), D5)Fill the rest using AutoFill.
Here, the formula concatenates the values in the range of cells B5:D5 with a line break in between them.
3.3 Add Line Break with TEXTJOIN Function
The TEXTJOIN function joins texts with a delimiter in between them.
Use the formula in cell E5:
=TEXTJOIN(CHAR(10), TRUE, B5:D5)Then apply AutoFill to get other cells with line breaks.
The formula joins the values within the range of cells B5:D5 with a line break after each cell value and ignores all the empty values.
Use Defined Name Instead of CHAR(10) in Excel Formulas
As we’re using CHAR(10) repeatedly in the formulas, we can set an alternative short name for it.
Go to the Formulas tab > click Defined Names drop-down > select Define Name.
In the pop-out dialog box, input
Name > LB
Scope > Workbook
Refers to > =CHAR(10)
Press OK. Now you can type LB instead of CHAR(10) in the formulas. For example,
=B5 & LB & C5 & LB & D5How to Enable Wrap Text Feature in Excel (to Line Break Automatically)
The Wrap Text feature generally helps to avoid the cell content overlapping on the next cell and tries to give a clearer view of what is inside the cell. It cannot insert line breaks where we need them, but it does line break at the point where the text exceeds the column width of the cell.
The above image shows the cell contents in the B column getting spilled over onto the neighboring cells.
Select the B5:B11 range and go to the Home tab. Click Argument drop-down > select Wrap Text. Thus, the cell contents get adjusted by themselves to fit the column width and show the contents clearly by inserting line breaks where necessary.
How to Create a New Line in Formula Bar in Excel
Select the cell and go to the formula bar. Click and place the cursor where we need to do a line break. For Windows users, press Alt and Enter. For Mac users, press Control + Option + Enter.
This way you can get line breaks in the formula.
How to Remove Line Breaks in Excel
To remove line breaks, select C5:C11 and press Ctrl and H keys together.
The Find and Replace dialog box pops out. Input:
Find what >> Press Ctrl + J
Replace with >> A space
Lastly, press Replace All.
Adjust column widths and you’ll see the cell contents in a single line.
Things to Remember
1. Enable the Wrap Text feature when using & sign and functions to see the cell contents clearly. It’s a must for multiple lines to appear in a cell.
2. Adjust column widths and row heights when necessary to view all the cell contents.
3. The code for a line break on Windows is 10, so use CHAR(10). For Mac, it’s 13, so use CHAR(13).
Conclusion
These were all the methods on how to do a line break in Excel.
For a quick line break, use the keyboard shortcut by pressing Alt and Enter.
Apply the formula for doing line breaks in multiple cells at a time. Hope you found this guide informative and helpful. If you have any questions or suggestions let us know below. For more detailed guides like this, visit Exceldemy.com.
