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

Helios9

Board Regular
Joined
Oct 15, 2006
Messages
127
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.
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

thisoldman

Well-known Member
Joined
Jan 5, 2014
Messages
1,074
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.
 
Last edited:

Helios9

Board Regular
Joined
Oct 15, 2006
Messages
127
I had tried this before and it didn't work. Tried it again, now it works perfectly. Thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,335
Messages
5,595,569
Members
413,996
Latest member
mabelO

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top