Remove Line Breaks

Nanogirl21

Active Member
Joined
Nov 19, 2013
Messages
287
Office Version
  1. 2013
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


 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Little_Clubber

New Member
Joined
Nov 10, 2017
Messages
25
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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:

Nanogirl21

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,962
Office Version
  1. 365
Platform
  1. Windows
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Feb 1, 2016
Messages
2,962
Office Version
  1. 365
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,551
Messages
5,625,457
Members
416,107
Latest member
AVaes

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
Top