Remove Hard Carriage Return And Leading And Trailing Spaces

[David Montoya]

Mr. Rick,

Looking for a solution to my situation, I have found this 2013 post: https://www.mrexcel.com/forum/excel-...e-returns.html. I have tried to post my question here but it seems to be old and required to have a new post.

Your solution works great from me to remove hard carriage returns. I would like to see if possible to incorporate this to an existing private sub I am using to trim spaces from start and end of entered text strings.

My code is the following:

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
    For Each Cell In Intersect(Target, Range("A4:A1004"))
      If Len(Cell.Value) Then Cell.Value = Trim(Cell.Value)
    Next
    Application.EnableEvents = True
  End If
End Sub

I would like to include your solution to above mentioned, so that when entering information at the target range, the macro automatically removed the hard carriage returns as well

Sub ReplaceAtSignWithLineFeed_v3()
  Selection = Evaluate("IF(ROW(),SUBSTITUTE(" & Selection.Address & ",CHAR(10),""""))")
End Sub

By the way, my first macro portion takes some time to execute, if you can suggest a more effective and expedited way to do it, please let me know.

Thanks in advance for your assistance.