Find Replace to insert Carriage Returns

[Bobichael]

Someone please save me from losing my mind.

I'm working with a document that has several cells which require double carriage returns within cells. The location of each space is marked with an @ sign. A few weeks ago, I found an article online that explained how to do a find replace where the find value was '@' and the replace value was a series of numbers/characters that created a carriage return. When I doubled the carriage return code I got my two spaces. If I remember correctly, the string that I entered still appeared in the cell, so I needed to clean up afterwards by replacing that string with ' ', but it still left the line break.

I thought I bookmarked the page, but I can't find it again.

PLEASE HELP!!! What is the value that creates a carriage return?

Michael

 

[David Montoya]

Hi Rick,

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.

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