How to Insert a New Line in a Cell Using Formulas (5 Methods)

Method 1 – Using a Formula and Text Wrapping

Suppose you have student information, including student IDs, names, and departments, stored in different columns in an Excel sheet. You want to combine this data into a single cell and display it with line breaks. Here’s how you can achieve that:

1.1. Use CHAR(10) Function and Wrap Text

• In cell F5, enter the following formula to combine the text from cells B5, C5, and D5:

• The CHAR(10) function inserts a line break character.

• Turn on the Wrap Text command from the Home tab to display the combined text with line breaks.
• Drag the Fill Handle tool downward to apply the formula to other cells in column F.

1.2. Using the TEXTJOIN Function

• In cell F5, enter the following formula to combine the text values from cells B5, C5, and D5:

• The TEXTJOIN function concatenates text strings using a delimiter (in this case, the line break character represented by CHAR(10)).
• The TRUE argument ensures that empty cells are ignored in the result.
• Enable Wrap Text to display the combined text with line breaks.
• Copy the formula down to repeat the process for other cells.

Read More: How to Add New Line with CONCATENATE Formula in Excel 

Method 2 – Use a Keyboard Shortcut

When performing tasks in Excel, using keyboard shortcuts is the quickest way to execute operations. Inserting a new line is no exception. You can use keyboard shortcuts to add new lines both within cell values and cell formulas.

2.1. Inserting a New Line in Cell Values

• Suppose you have text values in columns B, C, and D, and you want to insert a new line between them in column F.
• Use the CONCATENATE function with the comma character (represented by CHAR(44)) to join the text strings:

• Select the combined text strings in column F, right-click, and choose Paste Values to remove formulas and keep only the values.

• Position your cursor where you want to insert the new line and press ALT+ENTER.

2.2. Adding a New Line in the Formula Bar

• Complex Formulas:
  • If you have a large formula (e.g., a Nested IF function) in a cell (e.g., cell F5), it can be challenging to read in a single line.
  • To make it more manageable, put your cursor in the formula bar.
  • After each IF statement, press ALT+ENTER to send the next part of the formula to a new line.
  • This splits the formula visually in the formula bar without affecting the cell value.

Now you can easily understand complex formulas by breaking them down into smaller parts.

Read More: How to Put Multiple Lines in Excel Cell 

Method 3 – Using the Find and Replace Feature

• Open your Excel workbook.
• Suppose you have specific characters in your text strings within cell values, and you want to insert a line break after those characters.
• Look at the formula in the image:

This formula combines the values from cells B5, C5, and D5, separated by commas (where CHAR(44) represents a comma).

• To insert a line break instead of the comma, follow these steps:
  • Press CTRL+H to open the Find and Replace dialog box.
  • In the Find What field, type CHAR(44 (which represents the comma).
  • In the Replace with field, type CHAR(10) (which represents a line break).
  • Click Replace All.

• Enable the Wrap Text feature for the cell containing the formula.
• Excel will replace the comma with a line break, resulting in a new line for each value.

Method 4 – Defining a Name for an Excel Formula

• Go to the Formulas tab and click the Define Name icon.

• In the New Name dialog box:
  • Insert a name (e.g., NewLine) in the Name field.
  • Type a comment in the Comment field (e.g., Start New Line).
  • In the Refers to field, type =CHAR(10).

• This creates a new formula named NewLine, which acts like the CHAR(10) function (inserting a line break).

• Now, use the following formula (similar to Method 1.1) but replace CHAR(10) with NewLine:

• Activate the Wrap Text command for the cell containing this formula, and Excel will insert a new line in place of the NewLine function.

Read More: How to Enter within a Cell in Excel 

Method 5 – Use VBA Code to Insert Line Break

• In your Excel workbook, you have three categories of data in column F, and you want to insert a new line after each category. Let’s apply a VBA macro to achieve this.
• Open the VBA editor window:
  • Press ALT+F11 to open the Visual Basic Editor window.
  • Click Insert and select Module.

• In the Module window, insert the following code:

Code:

Sub InsertingLineBreaks()

    Dim rng As Range
    Dim cell As Range
    Dim data As String
    
    ' Set the range to the desired column
    Set rng = Range("F:F")
    
    ' Loop through each cell in the range
    For Each cell In rng

        ' Check if the cell is not empty
        If Not IsEmpty(cell) Then

            ' Replace the separator with a new line
            data = Replace(cell.Value, " ", vbCrLf, 1, 2)
            
            ' Remove leading and trailing spaces
            cell.Value = Trim(data)

        End If

    Next cell

End Sub

• Click Run to execute the code.

• This will insert a new line in each cell within column F.

Troubleshooting – New Line in Excel Cell Not Working

If the new line in an Excel cell is not working, consider the following steps:

1. Check that the cell formatting is set to Wrap Text. The Wrap Text command must be enabled while using the CHAR(10) function.
2. Verify that the font size is not too large for the cell. If the font size is too large, the text may not wrap properly. Try reducing the font size.
3. Ensure there are no leading or trailing spaces in the text strings. These spaces can interfere with the line break code. You can use the TRIM function to remove any leading or trailing spaces from the text.

How to Remove New Line or Line Breaks in Cell Formula in Excel

To remove the line break in a cell formula, use the SUBSTITUTE function to replace the CHAR(10) function. Apply the following formula in a cell where you want to show the result after removing the line break:

This will replace the line break with an empty string.

Frequently Asked Questions

• How can I insert a line break in a cell’s formula bar without affecting the cell’s content?

By default, the formula bar in Excel does not support line breaks. However, you can achieve a similar effect by pressing ALT+ENTER within the formula bar. This will create a line break in the formula bar without affecting the actual cell content.

• Can I add line breaks in Excel cells using conditional formatting? 

No, conditional formatting in Excel is used for formatting cells based on specific conditions, but it doesn’t have a built-in feature to add line breaks within a cell’s content.

• Is it possible to add line breaks in Excel cells when using structured references in tables?

Yes, you can add line breaks in Excel cells when using structured references in tables. You can reference the table columns in your formula and use the CHAR(10) function.

Download Practice Workbook

You can download the practice workbook from here:

Related Articles

• How to Make Two Lines in One Cell in Excel

<< Go Back to New Line | Text Formatting | Learn Excel