Replacing hard returns in long text strings.
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. |
Replacing hard returns in long text strings.
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 - gsnu200815 "Geoff C" wrote: 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. |
Replacing hard returns in long text strings.
Well that was painless.
Many thanks, Geoff. "Gary''s Student" wrote: 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 - gsnu200815 "Geoff C" wrote: 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. |
Replacing hard returns in long text strings.
You are quite welcome!
-- Gary''s Student - gsnu200815 "Geoff C" wrote: Well that was painless. Many thanks, Geoff. "Gary''s Student" wrote: 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 - gsnu200815 "Geoff C" wrote: 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. |
All times are GMT +1. The time now is 07:44 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com