This post will explain that how to find Alt + Enter characters or line breaks in the range of cells and then replace it with space character or other specific characters in excel.
When you want to create a line break in a cell in excel, you just need to press Alt + Enter keys. So you can insert one or more line breaks in a cell to make the contents easier to read.
You can use the Find and Replace function to replace all Alt +Enter with space character. Or you can use the VBA Macro code to achieve the same result quickly.
Table of Contents
1. Find and Replace Line Break (Alt+Enter) with Space using Find and Replace Function
If you want to find a line break and then replace it with a space character, just do the following steps:
#1 select the cells that you want to find and replace
![find replace linebreak1](https://www.excelhow.net/wp-content/uploads/2018/07/find-replace-linebreak1-.gif)
#2 go to HOME tab, click Find&Select command under Editing group, and click Replace… menu from the drop down list or just press Ctrl +H keys to open the Find and Replace dialog box.
![find replace linebreak2](https://www.excelhow.net/wp-content/uploads/2018/07/find-replace-linebreak2.gif)
#3 click in the Find what text box, press Ctrl +J to enter the line break.
#4 click in the Replace with text box, type a space character or other characters as you need.
![find replace linebreak3](https://www.excelhow.net/wp-content/uploads/2018/07/find-replace-linebreak3.gif)
#5 Click Replace All to replace the line breaks with space characters.
![find replace linebreak4](https://www.excelhow.net/wp-content/uploads/2018/07/find-replace-linebreak4.gif)
2. Find and Replace Line Break with Space using VBA
You can write an Excel VBA Macro to find alt +enter and then replace it with a space character, following these steps:
#1 click on “Visual Basic” command under DEVELOPER Tab.
![Get the position of the nth using excel vba1](https://www.excelhow.net/wp-content/uploads/2017/11/Get-the-position-of-the-nth-using-excel-vba1.jpg)
#2 then the “Visual Basic Editor” window will appear.
#3 click “Insert” ->”Module” to create a new module.
![convert column number to letter3](https://www.excelhow.net/wp-content/uploads/2017/12/convert-column-number-to-letter3.jpg)
#4 paste the below VBA code into the code window. Then clicking “Save” button.
![find replace linebreak5](https://www.excelhow.net/wp-content/uploads/2018/07/find-replace-linebreak5.gif)
Sub ReplaceAltEnter()
Dim fRange As Range
On Error Resume Next
Set fRange = Application.InputBox("Select the range of Cells:", "Replace Alt Enter", Selection.Address, , , , , 8)
If fRange Is Nothing Then Exit Sub
fRange.WrapText = False
fRange.Replace Chr(10), " ", xlPart, xlByColumns
End Sub
#5 back to the current worksheet, then run the above excel macro. Click Run button.
![find replace linebreak6](https://www.excelhow.net/wp-content/uploads/2018/07/find-replace-linebreak6.gif)
#6 select the range of cells that you want to find, click OK.
![find replace linebreak7](https://www.excelhow.net/wp-content/uploads/2018/07/find-replace-linebreak7.gif)
#7 Let’s see the result.
![find replace linebreak8](https://www.excelhow.net/wp-content/uploads/2018/07/find-replace-linebreak8.gif)
3. Video: Find and Replace Line Breaks with Space
This Excel video tutorial where we’ll explore two efficient methods for managing line breaks in Excel – the ‘Find and Replace’ feature and VBA (Visual Basic for Applications).
Leave a Reply
You must be logged in to post a comment.