Excel: Inserts New Line in Cell Formula (5 Methods)

When working with large amounts of text data, users often encounter the need to insert line breaks within a cell. While this may seem like a simple task, many users need to be made aware of the proper formula syntax to achieve this. This article will explore different methods for inserting Excel a new line in cell formula and discuss their benefits and limitations.

Whether you’re a beginner or an advanced user, this article will help you make the most of Excel’s capabilities and improve your productivity.

Excel: Insert New Line in Cell Formula (5 Effective Methods)

When you want to store and handle text entries in a cell in Excel, you may need to insert certain parts of your text in a new line. You may also feel the necessity to start a new line in the formula bar. There are several methods for inserting Excel a new line in the cell formula. This section will cover 5 different unique and effective methods to insert new lines.

1. Start New Line in Cell with Formula Followed by Wrapping Text

Let’s say, we have some student’s information like ID, name of the students and their departments in different columns in an Excel sheet. First, we will combine the cell values in just one cell and then we will send the different data in a new line.

1.1. Use CHAR(10) Function and Wrap Text to Insert New Line

The CHAR function returns the character specified by the code number from the character set of your computer. And the CHAR(10) function inserts a line break in Excel.

• We have applied the following formula in cell F5 to combine the texts in cells B5, C5, D5.

• Then we must turn on the Wrap Text command from the Home tab. This will insert a new line in the cell.
• Drag the Fill Handle tool downward and all the texts of the cells in column F will be sent to the new line.

1.2. Use TEXTJOIN Function to Add New Line in Cell

We will now follow the same procedure, but here we will apply the TEXTJOIN function to combine the texts of cells B5, C5, D5 in cell F5. The TEXTJOIN function concatenates a list or range of text strings using a delimiter. Our formula here will be.

The formula joins together the text values in cells B5, C5, and D5, separated by a line break character (represented by CHAR(10)). The TRUE argument indicates that empty cells should be ignored and not included in the result.

After combining texts, enable the Wrap Text command and you will be able to insert a line break. Now copy the formula down to repeat the process for the next cells.

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

2. Use Keyboard Shortcut to Create New Line

To perform any type of task in Excel, using a keyboard shortcut provides the fastest way to execute any operation. In the case of inserting a new line, there is no difference. Using keyboard shortcut, you can insert a new line both in cell value and cell formula.

2.1. Insert New Line in Cell Value

This method will insert a new line in between text values in a cell. First, we will use the CONCATENATE function to join text strings of columns B, C, D in column F. Here, we will also use the CHAR(44) function to return a comma to separate texts.

• Now select the combined text strings of column F > right-click on the muse > select Paste Values

• This will remove the formulas and paste only the values.
• Put your mouse where you want to insert new line and press ALT+ENTER.

• This command will start a new line both in the formula bar where you put the cursor and in cell value.

2.2. Add New Line in Cell Formula Bar

When you have a large formula applied to a cell, it may look comparatively complex to understand and break down the formula. Let’s say, we have applied that type of large formula with the Nested IF function in cell F5.

This formula seems too complex to read in a single line.

Put your cursor to the formula bar and after every IF statement, press ALT+ENTER to send the next part of the formula to a new line. Repeat this for the parts you want to send to a new line. This will just make changes in the formula bar, not in cell value.

See! The formula is now easy to understand as it is split part by part.

Read More: How to Put Multiple Lines in Excel Cell 

3. Apply Find and Replace Feature to Add Line Break After Specific Character Automatically

You can use the Find and Replace feature of Excel to insert a new line after a specific character or by replacing a specific character. Suppose you have specific characters in your text strings of cell value. Replacing that specific character, you want to put a line break.

Look at the formula in the image.

Here, the formula combines the cells B5, C5, D5 separated by commas as CHAR(44) returns a comma.

Here we want to insert a line break by replacing the comma.

• Press CTRL+H to open the Find and Replace dialog box.
• In the Find What field, type what you want to replace (i.e., CHAR(44)).
• In the Replace with field, type what you want to put after replacement. As our intention is to insert new line, we have put CHAR(10).
• Click Replace All.

• Enable the Wrap Text feature.

As a result, Excel will replace the comma and insert a new line as a replacement.

4. Insert New Line by Defining Name to Excel Formula

This method involves defining a name to a formula to insert a new line. The work structure of this formula is similar to Method 1.1. But the steps are different.

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

• The New Name dialog box will pop up.
• Insert a name (i.e. NewLine) in the Name field.
• Type a comment in the Comment field.
• In the Refers to field, type “=CHAR(10)”.

• As a result, a new formula named NewLine with the comment “Start New Line” will be created. It will act just like the CHAR(10) function.

• Enter the following formula just like stated in Method 1.1 but as a replacement of CHAR(10), put NewLine.
• Activate the Wrap Text command.

Excel will now insert a new line in the place of the NewLine function.

Read More: How to Enter within a Cell in Excel 

5. Use VBA Code to Insert Line Break

We have 3 categories of data in column F and we want to insert a new line after each category. Let’s apply a VBA macro to perform this task. First, you have to launch 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 code below.

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 code will insert a new line in the cell.

New Line in Excel Cell Not Working

There are several reasons why the new line in an Excel cell is not working.

• Check that the cell formatting is set to Wrap Text. The Wrap Text command must be enabled while using the CHAR(10) function.
• Check 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 and see if that helps.
• Ensure that there are no leading or trailing spaces in the text strings. These spaces can interfere with the line break code and prevent it from working. 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 the cell formula, you can use the SUBSTITUTE function to replace the CHAR(10) function.

Apply the formula below in a cell where you want to show the result after removing line break.

This replaces the CHAR(10) function (which returns line break) with blank space and thus removes line break.

Frequently Asked Questions

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

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

3. 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).

Key Takeaways from the Article

• Line breaks in Excel cells can be added using the CHAR(10) function within a formula.
• To ensure line breaks are displayed correctly, the cell formatting should be set to Wrap Text, allowing the text to wrap within the cell.
• If the line break formula is not working, check for correct formula syntax, remove leading or trailing spaces, and consider copying the formula to a new cell or sheet.

Download Practice Workbook

You can download the practice book from the link below.

Conclusion

So, the process of inserting Excel a new line in a cell formula takes very simple and handy approaches. In the case of using a formula, the “Wrap Text” command must be enabled. But just applying a keyboard shortcut for a cell, it’s not necessary. VBA can also speed up the task for the automation process. However, if you have any queries, don’t forget to leave them in the comment box.

Related Articles

• How to Make Two Lines in One Cell in Excel

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