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

 

[Rick Rothstein]

Here is another macro that I think should work...

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

 

[Peter_SSs]

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]

.. or perhaps just this

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]

.. or perhaps just this

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]

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]

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]

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.