Removing Line Breaks in Cells [Solved]

[ddash3]

I imported some text data and set about cleaning it up. Each time the F2 key is pressed to edit a cell, the single line of data automatically breaks up into multiple lines and remains that way. Can I prevent that from happening and, more important, can I remove all such breaks from the sheet now they're there?

[Phil_]

Hi and welcome to the community!

I'm sorry I cannot reproduce the problem. Can you explain in more detail what you are doing exactly? Can you maybe include a screenshot?

Also, what version of OpenOffice on which operating system are you using?

KR, phil
_________________
OOo 2.4.1 & 3.0 • WinXP pro 32-bit + SP2 + current patches
Try Search OOo sites on http://www.oooninja.com - a great tool!
My fav. extension: http://extensions.services.openoffice.org/project/AltSearch.

[huwg]

The cells' alignment may be formatted to Wrap text automatically. Try removing that formatting.

[ddash3]

Thank you for the welcome, Phil, and for taking a look at my question. The version of OpenOffice running here is 2.3.0, and the operating system is Windows 2000 v. 5.00.2195, Service Pack 4.

What I am doing is taking long unwieldy tables from a seller's catalogue and cutting and pasting them into Calc so as to be able to sort and compare. Importation is via clipboard cut & paste. The original material contains embedded line breaks, and that's probably the source of the problem. Screen shot of product listing: http://www.mediafire.com/imageview.php?quickkey=03embjcbkga&thumb=4 and a text file of the data obtained therefrom: http://www.mediafire.com/?9ddzt3cwt15

There are some 40 models displayed on that page, and I thought making a spreadsheet would be a good learning experience with at least some practical value.

Strangely the embedded line breaks do not manifest until a cell has been edited with the F2 key, though they do interfere with search & replace operations.

Here is an example of two processed records, one just before and one just after an F2 edit:
http://www.mediafire.com/imageview.php?quickkey=3xgvodxwi1c&thumb=4

Apparently the line breaks were latent until being activated by F2. The source data contain the hex codes 0D0A, i.e., <Carriage Return><Line Feed>.

The question seems to boil down to, Is there a way to search for and replace control codes like 0D0A hex? I have not found a way to enter such codes in the "Find & Replace" window.

Here's the sheet as it now stands: http://www.mediafire.com/?2zxzzt93dmj

Ddash3

[drking]

I don't think you have line breaks in your sheet - using a regular expression search doesn't find them. But I think you have some other odd characters.

I'll try to find out what they are and raise an issue (because this shouldn't happen I think)

In the meantime one solution is to take your existing sheet, then on another sheet put =T(Sheet1.A1) in cell A1; copy it down the column

You'll now see your text; the T() function has removed the odd characters.

Now copy that text, and on a third sheet do Edit-Paste Special, and paste *just* the text. the other sheets can be disgarded.


That's a horrible way to do it, but it is a workaround.

[drking]

Ah, I think it is either OD or 0A, which is being held internally by Calc but displaying/processing as a space. Yuk.

[Villeroy]

Type
a[Ctrl+Enter]b
to put two lines into a cell.
The funny thing is, that the formula language sees an empty space (ascii 32):

[huwg]

[drking]

Issue 83666 it is then

I was wrong that the line breaks cannot be found (I'd just be using Excel and didn't refocus properly). So the simple way to delete all the line breaks is to use the find / replace dialog; enter \n in the find box, choose 'regular expressions' (click the More button if necessary) and then 'replace all'.

[ddash3]

Thank you for the =T() function workaround, drking. As you say, it's an awkward way to have to do it, but it works.

The original data displayed in Internet Explorer contains the characters 0D0A hex, or char(13)& char(10). At least it does when cut and pasted into an MS DOS text file and saved as such, then loaded into a hex viewer. That's the traditional way in MS DOS to transmit the carriage return + line feed codes, aka Newline. Exactly how 0D0A is stored in Calc I have no idea, but it displays as a space (char(32)), as Villeroy points out, and that is misleading to say the least. I agree with huwg that it seems to be Issue #83666 again. The issue deserves more attention in my opinion. It seems to affect directly or indirectly several other posts by other participants on the form in recent days.

Inserting "\n" in the Find & Replace dialog box and chosing "regular expressions" does indeed make possible a global search & replace operation, which is what I came looking for. That will be handy in more places than one. I'd tried the key combination <Ctrl>+<Enter> with mixed results.

Does anybody know if it is possible to limit the F&R operation to a specific selected range of cells rather than the whole sheet? That would be a nice feature.

Thanks to all of you for your help!

[drking]

Yep, under the More Options button is a check box for 'current selection only'

[ddash3]

OK, thanks drking. I just now found a function called CLEAN() that removes all nonprintable characters within a string. That seems to do the job as well. Enter "=CLEAN(B1)" into a cell and fill it down the column length to strip any control codes from column B.

[Villeroy]

CLEAN() does nothing in this case.
A1: A[new_line]B
B1: =CLEAN(A1) => "A B"
C1: =A1=B1 =>TRUE

[ddash3]

Does CLEAN do nothing, or does it replace Newline with a space and conclude (erronerously) that the two cells A1 and B1 are equivalent? 83666 once more?

[Villeroy]

The formula language sees new lines as spaces.
Refering to my previous A1 having A[new_line]B
=A1="A B" -->TRUE