How to Replace a Character with a Line Break in Excel (3 Easy Methods)

In this article, I am going to explain how you can replace a character with a line break in Excel. While working with data sometimes we might need to replace some characters and add a line break. This particular article will help you with it.

How to Replace a Character with a Line Break in Excel: 3 Easy Methods

I am describing 3 easy methods to replace a character with a line break.

Suppose we have a dataset of some movie names, their genre, and release years.

1. Use Find & Replace Feature to Replace a Character in Excel

By using the find and replace function you can replace a character at a glance.

Steps:

• Select the cells.
• Press: Ctrl+H.

• A new window will appear named “Find and Replace”.
• In the “Find what” section type “,” as we are replacing this character.
• Now press Ctrl+Shift+J in the “Replace with” section.
• Click “Replace All”.

• A confirmation with replacement will appear.
• Press OK.

• Here we replaced our character(,) and also added a line break.

Read More: Find and Replace Line Breaks in Excel

2. Run a VBA Code to Replace a Character with a Line Break in Excel

Using VBA code you can replace a character with a line break.

Steps:

• Choose the cells and press Alt+F11.

• “Microsoft Visual Basic for Applications” will appear.
• From “Insert” select “Module”.

• In the module, the window applies the following code-

Sub ReplaceCharacter()
Dim Cell As Range
  For Each Cell In Selection
    Cell.Value = Replace(Cell, ",", vbLf)
  Next
End Sub

• Now run the code.

• Thus you can get your character replaced with a line break.

Read More: How to Make Excel Go to Next Line Automatically

3. Perform SUBSTITUTE Function to Replace a Character with a Line Break

With the help of the SUBSTITUTE function, you can replace a character with a line break.

Steps:

• Choose a cell. Here I have chosen cell (D5).
• Apply the formula-

Where,

• The SUBSTITUTE function replaces characters in a given string.
• The CHAR function returns a specific character when a valid number is given.

• Press Enter.
• Drag down the “Fill handle” to get the desired output in every cell.

• As you can see we got our desired cells with a line break replacing the character(,).

Read More: How to Replace Line Break with Comma in Excel

Things to Remember

• If you are using a Mac, put CHAR(13) instead of CHAR(10).

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

I hope you find this article very useful. We would like to hear from you if you find any problems regarding this article. Thanks!

Related Articles

• How to Space Down in Excel
• How to Remove Line Breaks in Excel
• How to Do a Line Break in Excel
• [Fixed!] Line Break in Cell Not Working in Excel

<< Go Back to Line Break in Excel | Text Formatting | Learn Excel