How to Make Line Continue in Excel

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)

• Inserting a Line Break Using a Keyboard Shortcut
• Inserting Line Breaks Using Formulas
• Using Find and Replace (the CONTROL J Trick)

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 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:

=A2&CHAR(10)&B2&(CHAR(10)&C2)

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:

=CONCAT(A2,CHAR(10),B2,CHAR(10),C2)

or the old CONCATENATE formula in case you're using older versions of Excel and don't have CONCAT

=CONCATENATE(A2,CHAR(10),B2,CHAR(10),C2)

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)

=TEXTJOIN(CHAR(10),TRUE,A2:C2)

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)

Now you can use LB instead of =CHAR(10).

So the formula to combine address can now be:

=A2&LB&B2&LB&C2

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:

1. Select the cells in which you want to replace the comma with a line break
2. Click the Home tab
3. 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.
4. In the Find and Replace dialog box, enter comma (,) in the Find What field.
5. 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
6. Click on Replace ALL.
7. 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

duttongrou1955.blogspot.com

Source: https://trumpexcel.com/insert-line-break-in-excel/

Share this post