Indeed, Microsoft Excel is renowned for managing and manipulating data; However, formatting text is also an essential aspect of data analysis. What if you need to insert a new line character in your Excel data set? With this question in mind, this article shows 4 valuable ways to insert new line char in Excel. In addition, we’ll also learn to insert a new line in the cell formula in Excel.
How to Insert a New Line Using CHAR Function in Excel: 4 Ways
First and foremost, let’s consider the list of Best Selling Books dataset shown in range B4:D10, which contains the Book Name, the Author name, and the Genre. Here, we want to combine the three pieces of information into one cell and insert a new line char between them. Therefore, let’s glance at each method in detail and with the appropriate illustrations.
Here, we have used the Microsoft Office 365 version, you may use any other version according to your convenience.
1. Using the CHAR Function with Ampersand Operator
We’ll use the CHAR function with the ampersand (&) operator to insert a line break character after each value.
📌 Steps:
- Go to cell E5 >> Enter the formulas given below.
=B5 & CHAR(10) & C5 & CHAR(10) & D5Here, the cells B5, C5, and D5 refer to the Book Name, Author, and Genre respectively, while the CHAR(10) represents the line break character.
As a note, make sure to enable the Wrap Text option in the Ribbon.
Read More: [Fixed!] CHAR(10) Is Not Working in Excel
2. Combining TEXTJOIN Function with CHAR Function
Alternatively, we can use the TEXTJOIN function to insert a new line char in Excel. Here, the TEXTJOIN function joins a list of strings with a specified delimiter, in this case, the line break character.
📌 Steps:
- Initially, move to cell E5 >> Type in the equation given below.
=TEXTJOIN(CHAR(10),TRUE,B5:D5)Formula Breakdown:
- TEXTJOIN(CHAR(10),TRUE,B5:D5) → Concatenates a range of text strings with a delimiter. Here, CHAR(10) is the delimiter argument which is the line break character. Next, TRUE is the ignore_empty argument, which ignores empty cells. Lastly, range B5:D5 is the text1 argument, which represents the Book Name, Author, and Genre columns.
Read More: How to Use Code 9 with Excel CHAR Function
2. Combining CONCATENATE and CHAR Functions
Conversely, another way to insert new line char in Excel involves using the CONCATENATE and CHAR functions. Specifically, the CONCATENATE function joins the texts while the CHAR function returns the new line character.
📌 Steps:
- Navigate to cell E5 >> Insert the expression given below.
=CONCATENATE(B5, CHAR(10), C5, CHAR(10), D5)
Read More: Character Codes for CHAR Function in Excel
4. Using Named Range with CHAR Function
For one thing, we can assign Named Range which makes it easier to interpret the formula. Now, allow us to demonstrate the process in the steps below.
📌 Steps:
- Jump to the Formulas tab >> Click the Define Name option.
- Afterward, enter a Name, here it is NewLine >> In the Refers to field, insert the formula below.
=CHAR(10)In this scenario, the CHAR(10) represents the line break character.
- Proceed to cell E5Â >> Enter the formula into the Formula Bar.
=B5&NewLine&C5&NewLine&D5For example, the B5, C5, and D5 cells point to the Book Name, Author, and Genre respectively, while the NewLine represents the Named Range for the new line character. Additionally, the ampersand (&) operator combines the string of text.
Read More: How to Use CHAR(34) Formula in Excel
Using Keyboard Shortcut to Insert New Line in Excel
Now, wouldn’t it be great if only there was a keyboard shortcut to insert a new line char in Excel? Well, you’re in luck because our next method answers this exact question.
📌 Steps:
- Copy cell B5 and paste it into cell C5.
- Place the cursor as shown in the image below >> Press the ALT + ENTER keys.
📃 Note: If you are a Mac OS user, use the CTRL + Command + Return keys on your keyboard.
- In a similar manner, insert a second line break to obtain the output shown below.
Likewise, repeat the same procedure for the cells below, and the final output should appear in the figure below.
Applying the Find & Select Option to Insert New Line in Excel
Furthermore, we can also apply the Find & Select feature to quickly insert new line char into multiple cells.
📌 Steps:
- Select range C5:C10 >> Click the Find & Select drop-down >> Choose the Replace option.
- In the Find what field, insert a comma (,) >> For the Replace with field, hit the CTRL + J keys >> Click on Replace All option.
Finally, your output should appear in the screenshot below.
Utilizing VBA Code to Insert New Line in Excel
Last but not least, we can apply VBA Code to insert new line char in Excel.
📌 Steps:
- Navigate to the Developer tab >> Click the Visual Basic button.
This now launches the Visual Basic Editor in a new window.
- Next, go to the Insert tab >> Select Module.
For your ease of reference, you can copy the code from here and paste it into the window as shown below.
Sub Insert_New_Line()
Range("E5") = Range("B5") & vbNewLine & Range("C5") & vbNewLine & Range("D5")
Range("E6") = Range("B6") & vbNewLine & Range("C6") & vbNewLine & Range("D6")
Range("E7") = Range("B7") & vbNewLine & Range("C7") & vbNewLine & Range("D7")
Range("E8") = Range("B8") & vbNewLine & Range("C8") & vbNewLine & Range("D8")
Range("E9") = Range("B9") & vbNewLine & Range("C9") & vbNewLine & Range("D9")
Range("E10") = Range("B10") & vbNewLine & Range("C10") & vbNewLine & Range("D10")
End Sub
âš¡ Code Breakdown:
Now, let’s understand the VBA code used to insert a new line char in Excel.
- First of all, the sub-routine is given a name, here it is Insert_New_Line().
- Next, enter the cell references, here it is the B5, C5, D5 cells, and use the Range object to return their values.
- Then, use the vbNewLine constants to insert line breaks between the cell values.
- Lastly, enter cell E5 as the cell reference for the output cell.
- Close the VBA window >> Click the Macros button.
This opens the Macros dialog box.
- Following this, select the Insert_New_Line macro >> Hit the Run button.
The results should look like the picture given below.
How to Insert a New Line in Cell Formula in Excel
Furthermore, we can insert new line char in Excel formulas to make them easier to comprehend. In the following section, we’ll consider the Employee Sales Performance dataset, which shows the employee Name and their Sales in USD.
📌 Steps:
- Copy and paste the expression below into cell D5Â >> Place the cursor in the location shown below >> Hit the ALT + ENTER keys.
=IF(C5>2000,"Satisfactory",IF(C5=1000,"OK",IF(C5<2000,"Bad")))In this situation, C5 is the starting cell of the Sales column.
- Press the ALT + ENTER keys again.
Now, this should result in the output shown in the image below.
Lastly, drag the Fill Handle tool to copy the formula to the cell below.
How to Make Excel Go to Next Line Automatically
By using the ENTER button, we may quickly move to the following cell in Excel. But it’s not exactly simple to move to the following line in the same cell. There are two easy ways to go to next line automatically in Excel. One of the methods is using the Wrap Text toggle. Hence, let’s see it in action in case of multiple cells at once.
📌 Steps:
- To start with, copy range B5:B10 and paste them into range C5:C10.
- Select range C5:C10Â >> Click the Wrap Text toggle.
We’ve inserted a new line char.
Practice Section
We have provided a practice section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.
Download Practice Workbook
Conclusion
In short, this tutorial explores all the ins and outs of how to insert new line char in Excel. Now, we hope all the methods mentioned above will prompt you to apply them in your Excel spreadsheets more effectively. Furthermore, if you have any questions or feedback, please let me know in the comment section. Or, you can check out our other articles related to Excel functions on our website.
