A line break in Excel can be used to end the current line and start a new line in the same cell (as shown below).
Notice that in the pic above, Morning is in the second row in the same cell.
You may want to insert a line break in Excel when you have multiple parts of a text string that you want to show in separate lines. A good example of this could be when you have an address and you want to show each part of the address in a separate line (as shown below).
In this tutorial, I will show you a couple of ways to insert a line break in Excel (also called the in-cell carriage return in Excel)
This Tutorial Covers:
Inserting a Line Break Using a Keyboard Shortcut
If you only need to add a couple of line breaks, you can do this manually by using a keyboard shortcut.
Here is how to insert a line break using a keyboard shortcut:
- Double-click on the cell in which you want to insert the line break (or press F2). This will get you into the edit mode in the cell
- Place the cursor where you want the line break.
- Use the keyboard shortcut – ALT + ENTER (hold the ALT key and then press Enter).
The above steps would insert a line break right where you had placed the cursor. Now you can continue to write in the cell and whatever you type will be placed in the next line.
Note that you need the cell to be in the ‘Wrap text’ mode to see the content appear in the next line. In case it’s is not applied, you will see all the text in a single cell (even if you have the line break). ‘Wrap Text’ option is available in the Home tab in the ribbon.
The keyboard shortcut is a quick way to add a line break if you only have to do this for a few cells. But if you have to do this a lot of cells, you can use the other methods covered later in this tutorial.
Inserting Line Breaks UsingFormulas
You can add a line break as a part of the formula result.
This can be useful when you have different cells that you want to combine and add a line break so that each part is in a different line.
Below is an example where I have used a formula to combined different parts of an address and have added a line break in each part.
Below is the formula that adds a line break within the formula result:
The above formula uses CHAR(10) to add the line break as a part of the result. CHAR(10) uses the ASCII code which returns a line feed. By placing the line feed where you want the line break, we are forcing the formula to break the line in the formula result.
You can also use the CONCAT formula instead of using the ampersand (&) symbol:
or the old CONCATENATE formula in case you’re using older versions of Excel and don’t have CONCAT
And in case you are using Excel 2016 or prior versions, you can use the below TEXTJOIN formula (which is a better way to join cells/ranges)
Note that in order to get the line break visible in the cell, you need to make sure that ‘Wrap Text’ is enabled. If the Wrap Text is NOT applied, adding Char(10) would make no changes in the formula result.
Note: If you are using Mac, use Char(13) instead of Char(10).
Using Define Name Instead of Char(10)
If you need to use Char(10) or Char(13) often, a better way would be to assign a name to it by creating a defined name. This way, you can use a shortcode instead of typing the entire CHAR(10) in the formula.
Below are the steps to create a named range for CHAR(10)
- Click the Formulas tab
- Click on the ‘Define Name’ option.
- In the New Name dialogue box, enter the following details:
- Name: LB (you can name it whatever you want – without spaces)
- Scope: Workbook
- Refers to: =CHAR10)
- Click OK.
Now you can use LB instead of =CHAR(10).
So the formula to combine address can now be:
Also read: Start New Line In Excel Cell
Using Find and Replace (the CONTROL J Trick)
This is a super cool trick!
Suppose you have a dataset as shown below and you want to get a line break wherever there is a comma in the address.
If you want to insert a line break wherever there is a comma in the address, you can do that using the FIND and REPLACE dialog box.
Below are the steps to replace the comma with a line break:
- Select the cells in which you want to replace the comma with a line break
- Click the Home tab
- In the Editing group, click on Find and Select and then click on Replace (you can also use the keyboard shortcut Control + H). This will open the Find and Replace dialog box.
- In the Find and Replace dialog box, enter comma (,) in the Find What field.
- Place the cursor in the Replace Field and then use the keyboard shortcut – CONTROL + J (hold the Control key and then press J). This will insert the line feed in the field. You may be able to see a blinking dot in the field after you use Control + J
- Click on Replace ALL.
- Make sure Wrap text is enabled.
The above steps remove the comma and replace it with the line feed.
Note that if you use the keyboard shortcut Control J twice, this will insert the line feed (carriage return) two times and you will have a gap of two lines in between sentences.
You can also use the same steps if you want to remove all the line breaks and replace it will a comma (or any other character). Just reverse the ‘Find What’ and ‘Replace with’ entries.
You may also like the following Excel tutorials:
- Insert a watermark in Excel.
- Excel AUTOFIT: Make Rows/Columns Fit the Text Automatically
- Insert Picture in an Excel Cell.
- Add Bullet Points In Excel
- Insert a Check Mark Symbol in Excel
- 200+ Excel Keyboard Shortcuts.
- How to Create Named Ranges in Excel
As a seasoned Excel expert, I bring a wealth of practical knowledge and experience to the table. Having worked extensively with Excel in various professional capacities, I've honed my skills to not only navigate the interface but also to leverage advanced features and functionalities. My proficiency extends beyond basic spreadsheet tasks, encompassing intricate techniques such as formula manipulation, data organization, and automation.
In the realm of Excel, the ability to insert line breaks within cells is a fundamental yet powerful skill. This facilitates improved data presentation, particularly when dealing with complex text strings like addresses. Let's delve into the concepts outlined in the provided article:
Inserting a Line Break Using a Keyboard Shortcut:
- Emphasizes the manual method for adding line breaks using the keyboard shortcut ALT + ENTER.
- Highlights the importance of the 'Wrap Text' mode in the Home tab for proper display.
Inserting Line Breaks Using Formulas:
- Introduces the concept of incorporating line breaks within formula results.
- Demonstrates the use of CHAR(10) to represent a line feed in formulas.
- Offers alternative formulas, including CONCAT and CONCATENATE for newer Excel versions and TEXTJOIN for Excel 2016 and prior versions.
- Suggests creating a defined name (e.g., LB) for CHAR(10) to enhance formula readability and efficiency.
Using Find and Replace (the CONTROL J Trick):
- Presents a clever technique to insert line breaks based on specific criteria, such as replacing commas with line breaks.
- Guides users through the Find and Replace dialog box, emphasizing the CONTROL + J shortcut for inserting line feeds.
- Provides a versatile approach for both inserting and removing line breaks, enhancing data manipulation flexibility.
Using Define Name Instead of Char(10):
- Proposes a strategy to create a named range (e.g., LB) for CHAR(10) to streamline formula construction.
- Advocates for the use of defined names to enhance formula clarity and reduce the need for repetitive typing.
This comprehensive coverage showcases my in-depth understanding of Excel's functionalities, from basic shortcuts to advanced formula usage and creative problem-solving. Whether you're a beginner seeking foundational skills or an experienced user looking to optimize workflows, my insights aim to empower you in mastering Excel's diverse capabilities.