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

Get FREE Advanced Excel Exercises with Solutions!

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.

Use Find and Replace Function Replace a Character in Excel

  • 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”.

Use Find and Replace Function Replace a Character in Excel

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

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

Use Find and Replace Function Replace a Character in Excel

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.

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

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

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

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

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

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-
=SUBSTITUTE(B5, ",",CHAR(10))

Where,

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

Perform SUBSTITUTE Function to Replace a Character with a Line Break

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

Perform SUBSTITUTE Function to Replace a Character with a Line Break

  • 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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Wasim Akram
Wasim Akram

Wasim Akram holds a BSc degree in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Over the past 2 years, he has been actively contributing to the ExcelDemy project, where he has authored more than 150 articles. Now, he is working as an Excel VBA and Content Developer. He likes learning new things about Microsoft Office, especially Excel VBA, Power Query, Data Analysis, and Excel Statistics. He is also very interested in machine learning and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo