New Line in Cell Formula in Excel (4 Cases)

There are several methods to insert New Line in Excel Cell Formula. Among them, we will demonstrate to you 4 easy and effective methods.

Download Workbook


4 Cases of New Line in Cell Formula in Excel

The following Teacher’s List table shows columns with ID No, Name, and Department. We will use 4 methods to insert Excel new lines in the cell formula. Here, we used Excel 365. You can use any available Excel version.

How to Insert New Line in Cell Formula in Excel


Case-1: Add New Line in Cell with Formula

Here, we will use the CONCATENATE function to put together the values from multiple cells, and we will insert new lines in the Excel cell formula.

➤ First of all, we have to type the following formula in cell F5.

=B5&CHAR(10)&C5&CHAR(10)&D5

Here, the CHAR(10) function helps us to insert line breaks in between.

➤ Now, we will press ENTER.

We can see the result in cell F5.

➤ Here, we need to wrap the text to get the view of lines. To do so, we will select cell F5 and click on Wrap Text.

We can see the line break in cell F5.

➤ We will drag down the formula with the Fill Handle tool.

How to Insert New Line in Cell Formula in Excel

We can see that from cells F5 to F9 the information from multiple cells is now put together in a single cell.

Here, we increased the row height to adjust the line breaks within the cells.

How to Insert New Line in Cell Formula in Excel

Read More: How to Add New Line with CONCATENATE Formula in Excel (5 Ways)


Case-2: Using TEXTJOIN Function to Insert New Line in Cell Formula

In this method, we will use the TEXTJOIN function to put together the values from multiple cells and insert new lines in a cell. In Excel for Office 365, Excel 2019, and Excel 2019 for Mac, we can use the TEXTJOIN function.

➤ To begin with, we will type the following function in cell F5.

=TEXTJOIN(CHAR(10),TRUE,B5:D5) 

Here,

  • CHAR(10) → returns a carriage between each of the texts.
  • TRUE → triggers the formula to ignore blank cells.
  • B5:D5 → the range to join.

➤ Now, press ENTER.

We can see the result in cell F5

➤ We will drag down the formula with the Fill Handle tool.

How to Insert New Line in Cell Formula in Excel

Finally, we can see that all the cells from F5 to F9 contain 3 lines of information in one cell.

How to Insert New Line in Cell Formula in Excel

Read More: How to Put Multiple Lines in Excel Cell (2 Easy Ways)


Case-3: Using Replace Option

In this method, we will insert line breaks within a cell using the Replace option.

Before doing that we need to combine the information together. And here, we’ll separate the combination with comma first. Later we’ll replace the comma with new line.

➤ First of all, we will write the following formula in cell F5.

=B5&CHAR(44)&C5&CHAR(44)&D5

Here, the CHAR(44) function helps us to insert commas in between.

➤ Now, we will press ENTER.

We can see the result in cell F5.

➤ We will drag down the formula with the Fill Handle tool.

Finally, we can see that all the cells from F5 to F9 contain commas in between the information in one cell.

Now, we want to replace these commas with line breaks.

➤ To do so, first of all, we will select the range.

➤ Then, we will go to the Home tab > select Editing option > select Find&Select > select

Replace option.

Now, a Find and Replace window will appear.

➤ We will type CHAR(44) in the Find what box, and type CHAR(10) in the Replace with box.

➤ Click Replace All.

A Microsoft Excel window will appear.

➤ We will click OK.

Now, we can see there is no comma in between the information in the cells F5 to F9.

How to Insert New Line in Cell Formula in Excel

➤ Now, to see the line break in the cell, we will select cell F5, and we will click on Wrap Text.

Finally, we can see line breaks in cell F5.

How to Insert New Line in Cell Formula in Excel

You can do the same for the other cell following the process.


Case-4: Formula Arguments in New Line

In the following Teacher’s List table, we add a Salary column, and we will type a formula in the Salary Type column.

How to Insert New Line in Cell Formula in Excel

➤ Here, we typed the following formula in cell F5.

=IF(E5>1000,"Great",IF(E5=1000,"Satisfactory",IF(E5<1000,"Bad")))

➤ Now, we press ENTER.

We can see the result in cell F5.

➤ We will drag down the formula with the Fill Handle tool.

We can see the salary type in the Salary Type column.

How to Insert New Line in Cell Formula in Excel

➤ Now, if we click on cell F5, we see the formula in one line.

We want this formula in new lines.

To do so, place the mouse cursor ahead of the desired parameter which you are supposed to move, you can do this from the cell or in the Formula Bar, then, press ALT+ENTER.

➤ Here, we put our mouse cursor before IF, and after that, we press ALT+ENTER.

➤ Now, we press ENTER to complete the formula and step out of the edit mode.

Finally, we can see the formula in new lines.

How to Insert New Line in Cell Formula in Excel

Read More: How to Go to Next Line in Excel Cell (4 Simple Methods)


Conclusion

Here, we tried to show you some easy and effective methods that will help you to insert new line in Excel cell formula. We hope you will find this article helpful. If you have any queries or suggestions, please feel free to know us in the comment section.


Related Articles

Afia Kona

Afia Kona

Hello, I am Afia Aziz Kona. I graduated in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology (BUET). I have an immense interest in technical writing and content development, therefore, I am working as a content developer at Exceldemy. In my spare time, I travel, watch movies, and cook different dishes.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo