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)
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. 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 Using Formulas
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:
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: 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
