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. Check out this link to learn more.


Download Practice Workbook

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


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

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 (6 Examples)


Similar Readings


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 the “Module”.

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

  • In the module, 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: VBA to Generate Multiple Lines in Email Body in Excel (2 Methods)


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 (3 ways)


Things to Remember

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

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

Wasim Akram
Wasim Akram

Hi! my name is Wasim Akram. I am a graduate in mechanical engineering from Ahsanullah University of Science & Technology. I am passionate about learning new things and writing articles. I write article about Microsoft Excel and it's my favourite thing to do.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo