How to Insert a New Line Using CHAR Function in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

excel char new line

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) & D5

Here, the cells B5, C5, and D5 refer to the Book Name, Author, and Genre respectively, while the CHAR(10) represents the line break character.

Using the CHAR function

As a note, make sure to enable the Wrap Text option in the Ribbon.

excel char new line using CHAR function

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.

Utilizing TEXTJOIN Function

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)

Combining CONCATENATE and CHAR Functions

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.

Using Named Range

  • Proceed to cell E5 >> Enter the formula into the Formula Bar.
=B5&NewLine&C5&NewLine&D5

For 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.

excel char new line using named range

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.

Employing Keyboard Shortcut

  • In a similar manner, insert a second line break to obtain the output shown below.

Pressing ALT and ENTER keys

Likewise, repeat the same procedure for the cells below, and the final output should appear in the figure below.

excel char new line with keyboard shortcut


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.

Applying the Find and Select Option

  • In the Find what field, insert a comma (,) >> For the Replace with field, hit the CTRL + J keys >> Click on Replace All option.

Using Find and Replace option

Finally, your output should appear in the screenshot below.

excel char new line using find and select option


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.

Utilizing VBA Code

This now launches the Visual Basic Editor in a new window.

  • Next, go to the Insert tab >> Select Module.

Inserting 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

VBA code for excel char new line

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.

Code explanation for excel char new line

  • 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.

Running VBA code

The results should look like the picture given below.

excel char new line using VBA Code


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.

How to Insert a New Line in Cell Formula in Excel

📌 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.

Using If function

  • 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.

excel char new line inserting in cell formula


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.

Enabling Wrap Text Toggle

  • Select range C5:C10 >> Click the Wrap Text toggle.

Click wrap text option

We’ve inserted a new line char.

excel char new line using wrap text option


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.

Practice Section for excel char new line


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.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Eshrak Kader
Eshrak Kader

Hello! Welcome to my Profile. I completed my BSc. at Bangladesh University of Engineering & Technology from the Department of Naval Architecture & Marine Engineering. Currently, I am conducting research & posting articles related to Microsoft Excel. I am passionate about research & development and finding innovative solutions to problems.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo