Cannot find hard line breaks in text. Cuts off at 1024 characters.

[Helios9]

In a column, many of the cells contain text, some of them containing hard line breaks which I would like to remove. I select the cells, use Ctrl. F, then use Alt 0010 to search the cells for hard line breaks. If I deliberately enter a line break into the text of one of the cells, Excel will still not identify it. Instead is will say "..cannot find the data you're searching for."

It seems as though Excel is cutting off the search at anything over 1024 characters in a cell. The text has about 8500 characters per cell and Search/Replace will not identify any line break past 1024 characters. I've tried bouncing the cells into and out of MS Word, but that too for some reason terminates at 1024 characters.

Any help with a solution would be appreciated. Thanks in advance.



Using: Excel 2003. Windows 7 Home.

 

[thisoldman]

The function CLEAN() will strip all linefeeds and tabs from the text in a cell. A linefeed is CHAR(10) and a tab is CHAR(9).

If the cell A1 contains your text, the formula,

=CLEAN(A1)

returns the text without linebreaks. I tested with Excel 2013 that the formula works on a sample text of over 2500 characters with linebreaks at positions 693, 1137 and 1546.

An alternative, if you wish to keep tab characters, is

=SUBSTITUTE(A1,CHAR(10)," ")

Note there is a space between the double quotes in the SUBSTITUTE formula. This will put a space where the linefeed was originally and keep sentences from running together.

There is a possibility the text contains carriage returns, CHAR(13). In that case, run the SUBSTITUTE formula again, with CHAR(13) replacing CHAR(10) in the formula.

 

[Helios9]

I had tried this before and it didn't work. Tried it again, now it works perfectly. Thanks!