Remove Hard Carriage Return And Leading And Trailing Spaces

[Excel Fox]

Hi David

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Cell As Range
  If Not Intersect(Target, Range("A4:A1004")) Is Nothing Then
    Application.EnableEvents = False
    With Intersect(Target, Range("A4:A1004"))
        .Value = Evaluate("IF(ROW(),TRIM(SUBSTITUTE(" & .Address & ",CHAR(10),"""")))")
    End With
    Application.EnableEvents = True
  End If
End Sub