Remove Line Breaks

Nanogirl21

Active Member
Joined
Nov 19, 2013
Messages
291
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


 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,981
Office Version
  1. 2016
Platform
  1. Windows
Here is another macro that I think should work...
VBA Code:
Sub RemoveMultipleLineFeeds()
  Application.ScreenUpdating = False
  With Intersect([AK:AZ], ActiveSheet.UsedRange)
    .Replace " ", "|", xlPart, , , , False, False
    .Replace vbLf, " "
    .Value = Evaluate("IF(" & .Address & "="""","""",TRIM(" & .Address & "))")
    .Replace " ", vbLf
    .Replace "|", " "
  End With
  Application.ScreenUpdating = True
End Sub
 
Last edited:

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,576
Office Version
  1. 365
Platform
  1. Windows
Hi, Peter
I tried your code, it didn't remove the line break if it is in the last line?
That's true. Since the OP didn't give any sample data like that I didn't worry about it. I'll amend the code if that turns out to be a possibility. :)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,576
Office Version
  1. 365
Platform
  1. Windows
.. or perhaps just this
VBA Code:
Sub Replace_Excess_Breaks_v2()
  With Range("AK1:AZ" & Columns("AK:AZ").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row)
    .Value = Evaluate("substitute(substitute(trim(substitute(substitute(" & .Address & ","" "",""#""),char(10),"" "")),"" "",char(10)),""#"","" "")")
  End With
End Sub
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,981
Office Version
  1. 2016
Platform
  1. Windows
.. or perhaps just this
Rich (BB code):
Sub Replace_Excess_Breaks_v2()
  With Range("AK1:AZ" & Columns("AK:AZ").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row)
    .Value = Evaluate("if(" & .Address & "="""","""",substitute(substitute(trim(substitute(substitute(" & .Address & ","" "",""#""),char(10),"" "")),"" "",char(10)),""#"","" ""))")
  End With
End Sub
I think you will need to add what I show in red above in order for your code to work correctly.
 

Peter_SSs

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

ADVERTISEMENT

I think you will need to add what I show in red above in order for your code to work correctly.
Code worked for me as is with blank cells in the range.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,981
Office Version
  1. 2016
Platform
  1. Windows
Code worked for me as is with blank cells in the range.
When I ran it on my XL2010 setup, it replaced every cell in the specified range (blanks and non-blanks) with the same data, namely, the contents of the first cell in the range. The change I proposed fixed the problem. I think Microsoft unintentionally "fixed" the problem I am having on the newer versions of Excel when they added Dynamic Arrays, but that "fix" is not backward compatible with older versions of Excel.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,576
Office Version
  1. 365
Platform
  1. Windows
When I ran it on my XL2010 setup, it replaced every cell in the specified range (blanks and non-blanks) with the same data, namely, the contents of the first cell in the range. The change I proposed fixed the problem. I think Microsoft "fixed" the problem I am having on the newer versions of Excel when they added Dynamic Arrays, but it is not backward compatible with older versions of Excel.
OK, fair enough - that may be the case. Thanks for the added information. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,191
Messages
5,640,755
Members
417,165
Latest member
Hilders1

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