Wasim Akram
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.
- 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 (6 Examples)
Similar Readings
- Excel VBA: Create New Line in MsgBox (6 Examples)
- How to Remove Line Breaks in Excel (5 Ways)
- [Fixed!] Line Break in Cell Not Working in Excel
- How to Go to Next Line in Excel Cell (4 Simple Methods)
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 the “Module”.
- 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.
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.
- 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 (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!
