Remove Hard Carriage Return And Leading And Trailing Spaces

**David Montoya** · 04-08-2019, 03:18 PM

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:

Code:

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

Code:

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.

**Excel Fox** · 04-08-2019, 10:29 PM

Hi David

Try this

Code:

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

**David Montoya** · 04-09-2019, 06:43 AM

**Excel Fox** · 04-09-2019, 08:10 AM

That seems to be a completely different requirement, unrelated to what you are doing above. Best to start a new thread with the code/sample workbook

**David Montoya** · 04-13-2019, 04:41 PM

I have resolved the issue of the conditional formatting by modifying the target.

Regarding the solution for the trim and carriage returns' removal, which work very efficiently, I am still running into a problem. My worksheet contains several other macros; one of the macros is to append the worked data to a different worksheet, and I am getting at the column "A" of the worksheet where I am applying your solution multiple rows with "#VALUE!" which when doing the append, it gives me incorrect results due to the contents of column "A".

I am wondering if possible to have your solution, instead of hard coded for a range, to have it dynamically to be triggered by an input to a cell of the column "A", and within the range "A4:A1004"

Thank you!

**DocAElstein** · 04-15-2019, 01:40 AM

Hello David Montoya,
I am finding it hard to understand what you are asking with this:

Originally Posted by David Montoya

I am wondering if possible to have your solution, instead of hard coded for a range, to have it dynamically to be triggered by an input to a cell of the column "A", and within the range "A4:A1004"

As I understand it, the effect of this code line, .._

Code:

  If Not Intersect(Target, Range("A4:A1004")) Is Nothing Then

_.. , is to trigger, if a cell or cells in the range "A4:A1004" have its or their value changed.

( Further, the effect of this, .._

Code:

    With Intersect(Target, Range("A4:A1004"))

, _.. is to restrict action to the cell or cells which were changed. )

That sounds close to what you are asking for.
So I am confused about what you are actually wanting.
It does not take much to coinfuse me. So I may have overlooked something, or missed the point
It might be easier if you could give us a workbook with a reduced size sample, of desensitized data, to help explain what you want and / or what the problem is that you are having.

Alan

P.s. Welcome to excelfox.com :-)