Remove empty lines from a cell and keep the line breaks

[sivakumar123]

Hi,

Is it possible to remove empty lines in a cell without removing the lien breaks that exists.
For example, A1 has the below value :

User1 User 2 User 3

I need it to look like :

User 1 User 2 User 3

Since there are numerous cells like this, it's painstaking to remove them manually.

Thanks and Regards

 

[Alex Blakenburg]

If you have a limited number of blank lines you can us find and replace. Where your find what is Ctrl+J twice and replace with is Ctrl+J once but you would have to click Replace All a few times until they are all gone.
If would be helpful if you updated your account profile to show what version of Excel you are using. If you had MS365 something like this might work for you.

=TEXTJOIN(CHAR(10),TRUE,TEXTSPLIT(A2,CHAR(10)))

A vba option:
Select a cell or cells to identify which columns to run the code on.
The cells must be contiguous ie you can use shift to select it but not Ctrl

Sub RemoveDuplicate_LF_inCells()
    ' This will update the entire column based on a contiguous range of selected cells
   
    Dim rng As Range
    Dim arr As Variant
    Dim splitCell As Variant, newText As String
    Dim i As Long, j As Long, k As Long
   
    Set rng = Intersect(Selection.EntireColumn, ActiveSheet.UsedRange)
    arr = rng.Value
   
    For i = 1 To UBound(arr)

        For j = 1 To UBound(arr, 2)
            splitCell = Split(arr(i, j), vbLf)
            newText = ""
            For k = 0 To UBound(splitCell)
                If splitCell(k) <> "" Then
                    newText = newText & vbLf & splitCell(k)
                End If
            Next k
           
            If newText <> "" Then
                newText = Right(newText, Len(newText) - 1)
                 arr(i, j) = newText
            End If
        Next j
    Next i
   
    rng.Value = arr
End Sub

 

[hagia_sofia]

Another option:

=SUBSTITUTE(A1,REPT(CHAR(10),2),CHAR(10))

 

[Alex Blakenburg]

Another option:

=SUBSTITUTE(A1,REPT(CHAR(10),2),CHAR(10))

That's the equivalent of what I was trying with Find & Replace and it only works if you have LFLF (LFx2) but not if you have more than that ie LFx3, LFx4 etc
With the Find & Replace you can hit Replace All multiple times until they are all gone. You would need multiple substitiutes or recursion to make it work.

 

[Scott Huish]

If you don't have Excel 365. You do need to turn on Wrap Text in the result cells.

Book1
	A	B
1	User1 User 2 User 3	User1 User 2 User 3
2	User1 User 2 User 3	User1 User 2 User 3
Sheet1

Cell Formulas
Range	Formula
B1:B2	B1	=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A1," ","~"),CHAR(10)," "))," ",CHAR(10)),"~"," ")

 

[hagia_sofia]

That's the equivalent of what I was trying with Find & Replace and it only works if you have LFLF (LFx2) but not if you have more than that ie LFx3, LFx4 etc
With the Find & Replace you can hit Replace All multiple times until they are all gone. You would need multiple substitiutes or recursion to make it work.

You are of course 100% right! The idea (or maybe wager) behind it is that it might be enough in this particular case.

 

[Peter_SSs]

I suggest that you update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you want to change the original data rather than use a formula in a different cell/column, you could try this macro with a copy of your data.

Sub RemoveEmptyLines()
  Dim RX As Object
  Dim a As Variant
  Dim i As Long
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = Chr(10) & "{2,}"
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    a = .Value
    For i = 1 To UBound(a)
      a(i, 1) = RX.Replace(a(i, 1), Chr(10))
    Next i
    .Value = a
  End With
End Sub

Before:

sivakumar123.xlsm
	A
1	Data
2	User1 User2 User3
3
4	abc def
5	User No 1 User No 2 User No 3 User No 4
6
Sheet3

After:

sivakumar123.xlsm
	A
1	Data
2	User1 User2 User3
3
4	abc def
5	User No 1 User No 2 User No 3 User No 4
6
Sheet3

 

[sivakumar123]

@Alex Blakenburg and @Scott Huish
You formulas worked like charm.

 =TEXTJOIN(CHAR(10),TRUE,TEXTSPLIT(A2,CHAR(10)))

=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A1," ","~"),CHAR(10)," "))," ",CHAR(10)),"~"," ")

@hagia_sofia
This formula did not work when the empty lines where at the top or bottom of the cell.

=SUBSTITUTE(A1,REPT(CHAR(10),2),CHAR(10))

@Peter_SSs
Absolutely. Thank you for the direction. I have updated my Profile as you suggested.

Thank You all for your time.

Best Regards
Siva

 

[Alex Blakenburg]

Thanks for providing us all with feedback. Glad we could help.

 

[Fuji]

vba, if incase of below situation.

Sub test()
    Dim a, i&
    With Range("a1", Range("a" & Rows.Count).End(xlUp))
        a = .Resize(, 2).Value
        With CreateObject("VBScript.RegExp")
            .Global = True
            For i = 1 To UBound(a, 1)
                .Pattern = "^\n+|\n+$"
                a(i, 1) = .Replace(a(i, 1), "")
                .Pattern = "\n{2,}"
                a(i, 1) = .Replace(a(i, 1), vbLf)
            Next
        End With
        .Value = a
    End With
End Sub

Before

Book1
	A
1	User1 User 2 User 3
2	User1 User 2 User 4
Sheet2

After

Book1
	A
1	User1 User 2 User 3
2	User1 User 2 User 4
Sheet2