Excel Formula Help: Delete Spaces before ever line breaks

[hasisanchez]

Hi Experts,

I am editing over 200k records and I need help with a formula..
I have a lot of texts in every cell with line breaks and there are spaces before each line breaks and these should go away. Can you help?

Thanks!

 

[mfexcel]

Select the range of your data
Ctrl G to open Find and Replace
Find what: SPACE and CTRL J ('note: you will see nothing but a tiny blinking dot at the bottom on the text box)
Replace what: CTRL J
Replace all

Hi Experts,

I am editing over 200k records and I need help with a formula..
I have a lot of texts in every cell with line breaks and there are spaces before each line breaks and these should go away. Can you help?

Thanks!

 

[Rick Rothstein]

Give this macro a try (change the column ranges highlighted in red to whatever column range(s) your text is in)...

Sub RemoveSpacesBeforeLineBreaks()
  Dim Cell As Range, CellText As String
  With Intersect(Range("C:E,H:H"), ActiveSheet.UsedRange)
    Do While Not .Find(" " & vbLf, , xlValues, xlPart) Is Nothing
      .Cells.Replace " " & vbLf, vbLf, xlPart
    Loop
  End With
End Sub

 

[mfexcel]

I mean CTRL H... ;p

Select the range of your data
Ctrl G to open Find and Replace
Replace all