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

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

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.

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

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.