How to remove invisible line break character

Question

I have big data at excel, and some cells contains html codes. These cells have line breaks in them. I tried to replace line breaks (Alt+010, \n) but excel said there is no char like this. When I copied cell to notepad, there is no line break. When I copied from notepad to phpmyadmin sql area or textpad, I see line breaks again. There are notepad, textpad and phpmyadmin sql area screenshots below. How can I remove these invisible line breaks?

Answer 1

This could be a problem with Carriage Return + Line Feed. When you press Alt+Enter in Excel it only incerts a Line Feed. But if you somehow get both Carriage Return + Line Feed in a cell that could leed to additional problems. See this page for solutions: https://www.ablebits.com/office-addins-blog/2013/12/03/remove-carriage-returns-excel/

Answer 2

Did you try to remove any unnecessary tab within the code? Also check for some trivial things like e.g string max length in your mysql database or editor's miscellaneous settings.

EDIT. oh, I forgot. It may be also caused by your language settings, check for default database's regional coding preset and if Turkish is currently supported.

Answer 3

Line breaks - do you mean the line breaks you could introduce in Excel with ALT+ENTER?

Then you could use Search / Replace option in Excel without need to copy your content to another tool:

Open it and introduce in Search for CTRL+J (you will receive a point displayed in the search field).

In Replace you could introduce what you want (nothing, a space, a semicolon, ...).

Select Replace all.

EDIT:

I've tested it by copying html from textpad to one cell using clipboard. With this the method described by me is not working.

But there is another solution: Open replace command, for "search string" introduce ALT-Key (keep it pressed), then introduce by using the numeric key pad (on the right side of a "standard" keyboard) the tree digits 0 1 0 and finally release ALT-Key (you will see a point displayed in the search field). Choose as replacement string what you want and choose replace all.

Answer 4

Function =clean() helped me. Find/replace with ALT+J worked to replace, but did not fully deleted all the invisible characters in the string, so the cell was still misbehaving with text in columns. The =clean() function finally removed all the invisible characters left there.