Replacing hard returns in long text strings.

[Geoff C]

I thought I knew how to do this, i.e. put ALT+0010 in the find box, replace
with something else. However when I try and do this in a file which contains
a lot of very long text strings (the first one is >5000 characters, the
largest nearly 10000), it doesn't work. Even if I try to replace "a" with
"b", it tells me "Formula is too long", and stops the replace function dead
in it's tracks. (this even though the field is not a formula)

I can copy an individual string to Word, and replace "manual line breaks" -
that works. However, I've got hundreds of these strings, and it has to be
done on a regular basis, so that isn't a viable option.

Any suggestions very welcome.
Thanks,
Geoff.

 

[Gary''s Student]

Try this simple macro:

Sub ReturnKiller()
' gsnuxx
Dim r As Range, hardret As String
hardret = Chr(10)
For Each r In ActiveSheet.UsedRange
v = r.Value
If InStr(v, hardret) > 0 Then
r.Value = Replace(v, hardret, "")
End If
Next
End Sub

 

[Geoff C]

Well that was painless.

Many thanks, Geoff.

Try this simple macro:

Sub ReturnKiller()
' gsnuxx
Dim r As Range, hardret As String
hardret = Chr(10)
For Each r In ActiveSheet.UsedRange
v = r.Value
If InStr(v, hardret) > 0 Then
r.Value = Replace(v, hardret, "")
End If
Next
End Sub

 

[Gary''s Student]

You are quite welcome!