Remove Line Breaks

[Nanogirl21]

I have multiple rows of data in 1 cell. Some of the cells have line breaks without any data in that line. How do I remove these? I've tried clean, trim, and substitute but did not have any luck. These were combining all the text into 1 single row. I want to keep the line breaks and text separate. I only remove the line breaks that do not have any information on them.

CURRENT INFORMATION	WANTED RESULTS (it looks like there is a line break on the first line, but it's not it's just the formatting on here)
(line break) Apples Bananas Grapes	Apples Bananas Grapes
Green (line break) Yellow Red	Green Yellow Red
(line break) (line break) Earth Wind Fire	Earth Wind Fire

 

[Little_Clubber]

The ASCII code for a line break is Alt 0010
Unfortunately the 0010 will only work on the number pad of a keyboard and not the top number line so hopefully you have a keyboard.

In your file, make sure you have either Everything, or Nothing selected as if you have a small selection, you'll be limited to just those cells.

Hold Ctrl and Press H
This is the keyboard link for Find and Replace.
In the Find What box, Hold Alt and whilst holding this, press 0010 then release Alt
It will look like nothing has happened, but there's a line break in the box

In the Replace with field, make sure there is nothing.
Then click Replace All

It will now replace all line breaks, with nothing, thus removing them from the entire sheet.
Let me know how you get on.
Thanks

 

[Peter_SSs]

Try this formula

=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A2," ","#"),CHAR(10)," "))," ",CHAR(10)),"#"," ")

This formula is in A8 below and copied down.

It will now replace all line breaks, with nothing

To me, that isn't what was asked for:

I only remove the line breaks that do not have any information on them.

 

[Nanogirl21]

Try this formula

=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A2," ","#"),CHAR(10)," "))," ",CHAR(10)),"#"," ")

This formula is in A8 below and copied down.

View attachment 3647




To me, that isn't what was asked for:

This worked perfectly. Is there a way to make it into a VBA for all cells in columns AK-AZ?

 

[Peter_SSs]

This worked perfectly. Is there a way to make it into a VBA for all cells in columns AK-AZ?

Are there any formula in those columns?
If so, do the formulas need to remain or can they be replaced by the formula results?

 

[Nanogirl21]

Are there any formula in those columns?
If so, do the formulas need to remain or can they be replaced by the formula results?

No formulas are in those columns, just the data. The data can be replaced with the clean no line break data.

 

[Peter_SSs]

OK, give this a try in a copy of your workbook.

Sub Replace_Excess_Breaks()
  Dim RX As Object
  Dim a As Variant
  Dim i As Long, j As Long, lr As Long, uba2 As Long
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "(^|" & Chr(10) & ")(" & Chr(10) & "+)"
  lr = Columns("AK:AZ").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  a = Range("AK1:AZ" & lr).Value
  uba2 = UBound(a, 2)
  For i = 1 To UBound(a)
    For j = 1 To uba2
      If Len(a(i, j)) > 0 Then a(i, j) = RX.Replace(a(i, j), "$1")
    Next j
  Next i
  Range("AK1:AZ" & lr).Value = a
End Sub

 

[Akuini]

Here's another way:

Sub a1120134a()
'https://www.mrexcel.com/board/threads/remove-line-breaks.1120134/
Dim tx As String
Dim c As Range
Dim rr As Long
 
rr = Columns("AK:AZ").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

For Each c In Range("AK1:AZ" & rr)
    If Len(c) > 0 Then
        
        tx = c.Value
            Do
            tx = Replace(tx, vbLf & vbLf, vbLf)
            Loop While InStr(tx, vbLf & vbLf)
        
        If Right(tx, 1) = vbLf Then tx = Left(tx, Len(tx) - 1)
        c.Value = tx
    
    End If
Next

End Sub

 

[Peter_SSs]

Here's another way:

Hi Akuini
I note that your code checks at the end for a trailing vbLf & if so removes it. Doesn't your code need a similar removal for a leading vbLf as per the OPS top row example (or my cell A2 example)?

@Nanogirl21
BTW, about how many rows of data will you have in columns AK:AZ?

 

[Akuini]

I note that your code checks at the end for a trailing vbLf & if so removes it. Doesn't your code need a similar removal for a leading vbLf as per the OPS top row example (or my cell A2 example)?

Ah, you're right. Here's the amended code:

Sub a1120134a()
'https://www.mrexcel.com/board/threads/remove-line-breaks.1120134/
Dim tx As String
Dim c As Range
Dim rr As Long
 
rr = Columns("AK:AZ").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

For Each c In Range("AK1:AZ" & rr)
    If Len(c) > 0 Then
        
        tx = c.Value
            Do
            tx = Replace(tx, vbLf & vbLf, vbLf)
            Loop While InStr(tx, vbLf & vbLf)
        
        If Right(tx, 1) = vbLf Then tx = Left(tx, Len(tx) - 1)
        If Left(tx, 1) = vbLf Then tx = Mid(tx, 2)
        c.Value = tx
    
    End If
Next

End Sub

Hi, Peter
I tried your code, it didn't remove the line break if it is in the last line?