replacing multiple Chr(13)s

[Dan]

How can I replace multiple occurences of Chr(13) with a
single Chr(10)?

The cells I'm working with have more than one Chr(13) and
I want to replace them with a single Chr(10).
The cells looks like:
some text Chr(13)
Chr(13)
some more text, more text Chr(13)
Chr(13)
Chr(13).

Selection.Replace will let me change a single occurence of
Chr(13) but I can't figure out how to get it to accept
more than one. I can accomplish this by using the MID
statement but that is very slow. All the multiple
occurences are in a single column.

Your assistance is greatly appreciated,
Dan

 

[Harald Staff]

Hi Dan

One way: Replace 100 in a row with 99, 99 with 98 and so on down to 1. Pure horror from
RAM's point of view -but RAM is pretty cheap those days:

Sub test()
Dim L As Long, S As String
S = "A" & Chr(13) & Chr(13) & Chr(13) & Chr(13) _
& Chr(13) & "b" & Chr(13) & Chr(13) & Chr(13) & _
Chr(13) & Chr(13) & Chr(13) & Chr(13) & Chr(13) & "c"

For L = 100 To 2 Step -1
S = Replace(S, String(L, 13), String(L - 1, 13))
Next
S = Replace(S, Chr(13), Chr(10))

MsgBox S
End Sub