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
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
#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.
#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.
#5 Click Replace All to replace the line breaks with space characters.
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.
#2 then the “Visual Basic Editor” window will appear.
#3 click “Insert” ->”Module” to create a new module.
#4 paste the below VBA code into the code window. Then clicking “Save” button.
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.
#6 select the range of cells that you want to find, click OK.
#7 Let’s see the result.