Remove Line Breaks

Nanogirl21

Active Member
Joined
Nov 19, 2013
Messages
330
Office Version
  1. 365
Platform
  1. Windows
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 INFORMATIONWANTED 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


 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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
 
Upvote 0
Try this formula

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

This formula is in A8 below and copied down.

1578618104357.png





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.
 
Last edited:
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
OK, give this a try in a copy of your workbook.

VBA Code:
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
 
Upvote 0
Here's another way:
VBA 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)
        c.Value = tx
    
    End If
Next

End Sub
 
Upvote 0
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?
 
Upvote 0
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:

VBA 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?
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top