Find and Replace Alt Enter (Line Breaks) with Space

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.

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

#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

#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

#5 Click Replace All to replace the line breaks with space characters.

find replace linebreak4

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

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

convert column number to letter3

#4 paste the below VBA code into the code window. Then clicking “Save” button.

find replace linebreak5
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

#6 select the range of cells that you want to find, click OK.

find replace linebreak7

#7 Let’s see the result.

find replace linebreak8

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

https://youtu.be/HSgHU7fDqSA

Leave a Reply