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.

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.

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.

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.

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.

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.

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

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.

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.

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

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.

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

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

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

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

• How to Replace a Character with a Line Break in Excel (3 Easy Methods)
• VBA to Generate Multiple Lines in Email Body in Excel (2 Methods)
• Find and Replace Line Breaks in Excel (6 Examples)
• Excel VBA: Create New Line in MsgBox (6 Examples)
• How to Remove Line Breaks in Excel (5 Ways)
• [Fixed!] Line Break in Cell Not Working in Excel