Insert Line Break in Excel to enhance the readability and structure of your data, making complex information more accessible and easier to understand. This simple yet impactful feature is crucial for professionals who deal with detailed reports, lengthy text data, or require clear presentation in cells. Whether you’re formatting a comprehensive financial statement, organizing product descriptions, or creating a neatly structured list, this guide will show you how to effectively add carriage returns, transforming your data’s appearance and improving the overall clarity of your spreadsheets. By mastering the ability to insert line breaks in Excel, you ensure that your data is not only accurately represented but also visually appealing and user-friendly.
You might occasionally wish a particular section of a text string to begin on a new line (insert line break) while using Excel to store and manipulate text data. Mailing labels or other personal information typed in one cell could be examples of multi-line text.
Starting a new paragraph is usually no difficulty in Office programs; you just need to press the Enter key on your keyboard. However, this operates differently in Microsoft Excel, where clicking the Enter key completes the entry and advances the cursor to the following cell. Consequently, how do you add a new line to Excel? In this article, you will discover how to insert line break in Excel.
This Tutorial Covers:
- Fastest way to create a new line
- Add multiple line breaks
- Line in formula to make it easier to read
- Inserting Line Breaks Using Formulas
- Using Define Name Instead of CHAR(10)
- Using Find and Replace (the CONTROL J Trick for a break after a specific character)
- How to remove carriage returns in Excel
1. Insert Line Break Using a Keyboard Shortcut / Keyboard Shortcuts to Insert Carriage Return
Using a keyboard shortcut is the fastest way to add a new line inside a cell:
- “Alt + Enter” is the Windows shortcut for a line break.
- “Control + Option + Return” or “Control + Command + Return” on a Mac are shortcuts for line feed.
Alternatively, you can press Option + Return in Excel 365 for Mac. It appears that the original Windows shortcut (Alt + Enter) is also functional for Mac because Option is the Mac equivalent of the Windows Alt key. If that doesn’t work for you, try the conventional Mac shortcuts.
The keys Command + Option + Return can be used to create a new line in Excel for Mac when accessed using Citrix. (We appreciate Amanda for the advice.)
Here are the ways to quickly add a new line in an Excel cell:
Step 1: Double-click the cell where a line break should be added. Write the opening sentence of the text. Place the cursor where you wish to break the line if the text is already in the cell.
Step 2: Holding “Alt” while pressing the “Enter” key on Windows. Hold “Control” and “Option” while pressing the “Return” key in Excel for Mac.
Step 3: To finish and leave the edit mode, press Enter. You will receive numerous lines in the Excel cell as a result. Make sure the Wrap text option is turned on if the text still appears in one line.
2. Add multiple line breaks
Press “Alt + Enter” twice or more times to create a gap of two or more lines between various text components. This will add a series of line feeds within a cell, as demonstrated in the following screenshot:
3. Line in formula to make it easier to read
Long formulas may occasionally benefit from being displayed in numerous lines to make them simpler to comprehend and troubleshoot. This is also possible using the Excel line break shortcut.
How to add line break in formula to make it easier to read is shown below:
Step 1: Place the cursor before the argument you want to move to a new line in a cell or in the formula bar.
Step 2: Then press “Ctrl + Alt”. Press “Enter” to finish the formula and get out of edit mode after that.
4. How to create a new line in Excel cell with a formula:/ Using CHAR Functions Insert Carriage Return in Excel Formula
A line break might be included in the formula result.
This can be helpful if you want to mix different cells and add a line break so that each component is on a different line.
Here is an illustration of how I combined distinct portions of an address using a formula and put line breaks between each part.
The formula that inserts a line break within the formula result is listed below:
=A2&CHAR(10)&B2&(CHAR(10))
The result of the calculation above includes the line break thanks to the use of CHAR(10). A line feed is produced by CHAR(10), which utilizes the ASCII code. We force the formula to break the line in the formula result by inserting the line feed where you desire the line break.
In addition, you can substitute the CONCAT formula for the ampersand (&) symbol:
=CONCAT(A2,CHAR(10),B2,CHAR(10))
Likewise, if you’re using an earlier version of Excel and don’t have CONCAT, the old CONCATENATE formula.
=CONCATENATE(A2,CHAR(10),B2,CHAR(10))
Additionally, if you’re using Excel 2016 or an earlier version, you can combine cells and ranges more effectively by using the TEXTJOIN function below.
=TEXTJOIN(CHAR(10),TRUE,A2:B2)
Keep in mind that “Wrap Text” must be enabled in order for the line break to appear in the cell. The result of the formula would not change if CHAR(10) were added but Wrap Text was NOT applied.
Note: If you’re using a Mac, substitute CHAR(13) for CHAR(10).
5. Using Define Name Instead of CHAR(10)
A better solution would be to give CHAR(10) or Char(13) a name by generating a defined name if you need to use them frequently. Instead of using the entire CHAR(10) in the formula, you can utilize a short code in this manner.
The steps to make a named range for CHAR(10) are listed below:
Step 1: select the “Formulas” tab. Select “Define Name” from the menu.
Step 2: Type the following information in the New Name dialogue box:
Name: LineBreak (you can name it whatever you want – without spaces)
Scope: Workbook
Refers to: =CHAR(10)
Click OK.
LineBreak can now be used in place of =CHAR(10). Thus, the following can be used to combine addresses:
=A2&LineBreak&B2&LineBreak
Also Read: How to Wrap Text in Excel (including shortcuts)
6. Using Find and Replace (the CONTROL J Trick for a break after a specific character)
This method is really awesome!
Imagine you have a dataset like the one below and you want to receive a line break whenever an address has a particular character, like a comma.
The FIND and REPLACE dialog box can be used to insert a line break wherever a comma appears in the address.
The steps to insert a line break in place of the comma are as follows:
Step 1: Pick out the cells where you wish to insert a line break in place of the comma.
Step 2: Select the “Home” tab. Click “Find and Select” in the “Editing” group, then click “Replace” (you may also use the keyboard shortcut Control + H to do this). The “Find and Replace” dialog box will then be displayed.
Step 3: In the “Find What” field of the “Find and Replace” dialog box, type a comma (,).
Step 4: Use the keyboard shortcut CONTROL + J while the cursor is in the “Replace Field” (hold the Control key and then press J). In the field, this will insert a line feed. After pressing Control + J, the field can show a blinking dot.
Step 5: Select Replace AL and then click OK. Make sure that Wrap text is turned on.
The comma is removed in the procedure above, and it is replaced with a line feed.
It should be noted that using the keyboard shortcut “Control+J” twice will insert the line feed (also known as a carriage return) twice, leaving a two-line gap between phrases.
If you wish to eliminate all line breaks and substitute a comma in their place, you can follow the same procedures (or any other character). The ‘Find What’ and ‘Replace with’ entries should be reversed.
7. How to remove carriage returns in Excel?
Here are the procedures for using Find and Replace to get rid of line breaks:
Step 1: Choose all the cells you want to replace or remove the carriage returns from.
Step 2: To launch the Find & Replace dialog box, press Ctrl+H. Enter Ctrl+J in the Find What area. You will notice a tiny dot, however it will appear to be empty.
Step 3: To replace carriage returns, type any value in the Replace With field. Typically, a space is used to prevent the unintentional joining of two words. Leave the “Replace With” section blank if all that has to be done is to remove the line breaks. Here, I’ve used a comma here.
Step 4: Click the “Replace All” option and take pleasure in the outcome!
Also Read: How to Capitalize First Letter in Excel?
You may be interested:
