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-questions/711593-find-replace-insert-carriage-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.

[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

[David Montoya]

It works very good, and expedite the process I used to have. Only a minor thing (I believe); in the same range I have a conditional formatting to highlight duplicates. When I use a clean up macro to start a new process, all the cell in the range, even if empty, they get highlighted as duplicates.

I there a solution to the duplicate issue?

Again thanks a lot for your help.


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgwhVTFaD469mW9wO194AaABAg.9gJzxwFcnPU9gORqKw5t W_ (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgwhVTFaD469mW9wO194AaABAg.9gJzxwFcnPU9gORqKw5t W_)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugyb8nmKKoXvcdM58gV4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugyb8nmKKoXvcdM58gV4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgwvvXcl1oa79xS7BAV4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgwvvXcl1oa79xS7BAV4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgxvIFArksPprylHXYZ4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgxvIFArksPprylHXYZ4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg)
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxUbeYSvsBH2Gianox4AaABAg.9VYH-07VTyW9gJV5fDAZNe (https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxUbeYSvsBH2Gianox4AaABAg.9VYH-07VTyW9gJV5fDAZNe)
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxLtKj969oiIu7zNb94AaABAg (https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxLtKj969oiIu7zNb94AaABAg)
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgyhQ73u0C3V4bEPhYB4AaABAg (https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgyhQ73u0C3V4bEPhYB4AaABAg)
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgzIElpI5OFExnUyrk14AaABAg.9fsvd9zwZii9gMUka-NbIZ (https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgzIElpI5OFExnUyrk14AaABAg.9fsvd9zwZii9gMUka-NbIZ)
https://www.youtube.com/watch?v=jdPeMPT98QU (https://www.youtube.com/watch?v=jdPeMPT98QU)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[Excel Fox]

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]

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]

Hello David Montoya,
I am finding it hard to understand what you are asking with this:
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, .._

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, .._

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 :-)