CODEWITHSUNDEEP
excel
user179516
user179516

Reputation: 337

How to remove new line breaks in excel

I have data in an excel sheet. In that sheet, there are manual breaks inserted for a new line. I want to remove all newline characters and have everything in one line.

How can I do that?

I tried selecting the column and in the replace under Find text box, I pressed ctrl+j and in the "replace with", I entered a space. But excel is showing that there is no data match to replace.

Please suggest how to remove manual new line breaks in the excel file?

Upvotes: 0

Views: 1717

Answers (3)

Mouthpear
Mouthpear

Reputation: 329

Removes all Line Breaks and leaves just one line. Select Cell or Range of Cells and run Macro.

Sub RemoveLineBreaks()
    Application.ScreenUpdating = False
    Dim rngCell As Range
    Dim strOldVal As String
    Dim strNewVal As String

    For Each rngCell In Selection
        If rngCell.HasFormula = False Then
            strOldVal = rngCell.Value
            strNewVal = strOldVal
            Debug.Print rngCell.Address

            Do

            strNewVal = Replace(strNewVal, vbLf, " ")

            If strNewVal = strOldVal Then Exit Do
                strOldVal = strNewVal
            Loop

            If rngCell.Value <> strNewVal Then
                rngCell = strNewVal
            End If
        End If
        rngCell.Value = Application.Trim(rngCell.Value)
    Next rngCell
    Application.ScreenUpdating = True
End Sub

Upvotes: 0

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

If the manual breaks were inserted with ALT-ENTER then this tiny macro will replace them all with spaces:

Sub qwerty()
    Cells.Replace Chr(10), " "
End Sub

Upvotes: 2

Michael Moulsdale
Michael Moulsdale

Reputation: 1488

A quick and dirty way would be to save the file as a text file and then use a utility such as grep or open in word / wordpad / sublimetext / textmate and do a global replace on the EOL character

Upvotes: 0

Related Questions