OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Removing Line Breaks in Cells [Solved]
Goto page 1, 2  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
ddash3
General User
General User


Joined: 11 Feb 2008
Posts: 8

PostPosted: Mon Feb 11, 2008 9:46 am    Post subject: Removing Line Breaks in Cells [Solved] Reply with quote

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?

Last edited by ddash3 on Fri Feb 15, 2008 10:36 am; edited 1 time in total
Back to top
View user's profile Send private message
Phil_
OOo Enthusiast
OOo Enthusiast


Joined: 12 Dec 2007
Posts: 170
Location: Germany

PostPosted: Tue Feb 12, 2008 1:31 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
huwg
Super User
Super User


Joined: 14 Feb 2007
Posts: 890

PostPosted: Tue Feb 12, 2008 4:09 am    Post subject: Reply with quote

The cells' alignment may be formatted to Wrap text automatically. Try removing that formatting.
Back to top
View user's profile Send private message
ddash3
General User
General User


Joined: 11 Feb 2008
Posts: 8

PostPosted: Tue Feb 12, 2008 4:37 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
drking
Power User
Power User


Joined: 25 Oct 2006
Posts: 91

PostPosted: Thu Feb 14, 2008 1:22 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
drking
Power User
Power User


Joined: 25 Oct 2006
Posts: 91

PostPosted: Thu Feb 14, 2008 1:35 am    Post subject: Reply with quote

Ah, I think it is either OD or 0A, which is being held internally by Calc but displaying/processing as a space. Yuk.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Feb 14, 2008 2:24 am    Post subject: Reply with quote

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):
Code:

=CODE(MID(A1;2;1))  --> 32
=A1 --> "a b"

When you want to replace line breaks with spaces, just reference the cells in question, copy, paste special text without formulas.
Back to top
View user's profile Send private message
huwg
Super User
Super User


Joined: 14 Feb 2007
Posts: 890

PostPosted: Thu Feb 14, 2008 5:09 am    Post subject: Reply with quote

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

Might be issue 83666:
http://www.openoffice.org/issues/show_bug.cgi?id=83666
Back to top
View user's profile Send private message
drking
Power User
Power User


Joined: 25 Oct 2006
Posts: 91

PostPosted: Thu Feb 14, 2008 7:37 am    Post subject: Reply with quote

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'.
Back to top
View user's profile Send private message
ddash3
General User
General User


Joined: 11 Feb 2008
Posts: 8

PostPosted: Thu Feb 14, 2008 9:26 am    Post subject: Reply with quote

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!
Back to top
View user's profile Send private message
drking
Power User
Power User


Joined: 25 Oct 2006
Posts: 91

PostPosted: Thu Feb 14, 2008 10:10 am    Post subject: Reply with quote

Yep, under the More Options button is a check box for 'current selection only'
Back to top
View user's profile Send private message
ddash3
General User
General User


Joined: 11 Feb 2008
Posts: 8

PostPosted: Thu Feb 14, 2008 10:34 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Feb 14, 2008 10:44 am    Post subject: Reply with quote

CLEAN() does nothing in this case.
A1: A[new_line]B
B1: =CLEAN(A1) => "A B"
C1: =A1=B1 =>TRUE
Back to top
View user's profile Send private message
ddash3
General User
General User


Joined: 11 Feb 2008
Posts: 8

PostPosted: Thu Feb 14, 2008 11:03 am    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Feb 14, 2008 11:11 am    Post subject: Reply with quote

The formula language sees new lines as spaces.
Refering to my previous A1 having A[new_line]B
=A1="A B" -->TRUE
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group