Re: problem with import of excel file, cells contain new lines
At 11:28 19/02/2015 +0100, Uwe Brauer wrote:
By the way, when I perform this operations for one cell say C2 and
then want to enhance it to the whole column, it seems that I can
only do it by dragging the boundary of the cells with the mouse. Is
this correct?
No. Dragging the fill handle (not the cell boundary) is indeed one,
often convenient way. But others are copying and pasting, as well as
using Edit | Fill | Down. For these, you will need to select the
target range, but you can easily do that using click at one end and
Shift+click at the other.
I am asking since the original file in question has a column of 300
rows and using the mouse proved to be very very slow, but
clean(C2:C300) did not work neither.
Actually, you can use formulae like that, where a function operates on a range:
o Select the first cell of the target range.
o Enter the formula.
o To complete the formula, don't press Enter or click the green
arrow. Instead, press Ctrl+Shift+Enter. The formula (visible in the
Input Line) has now grown enclosing braces, as
{=CLEAN(C2:C300)}
- but note that you cannot type the braces yourself. This is called
an array formula.
(By the way, I think I'd established that CLEAN() wouldn't work for
you: three applications would be necessary to remove all five tab
characters and you would still have the remaining space and
non-breaking space to deal with if you wanted to process the values
as numbers.)
I trust this helps.
Brian Barker
-
To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org
For additional commands, e-mail: users-h...@openoffice.apache.org
Re: problem with import of excel file, cells contain new lines
Brian == Brian Barker b.m.bar...@btinternet.com writes: At 18:29 18/02/2015 +0100, Uwe Brauer wrote: I run for example clean(C2) and it returns Err:522. C is the column with the problems That means that your formula is circular. I'm guessing that you have tried putting =CLEAN(C2) into C2 itself. Apart from the fact that this Right! I did this because I thought: if I copy the cell in a new column, and then want to replace the old one with the new one, I will have a problem: the new one depends on the old one and then everything gets deleted. But as you explain below: that can be avoided, thanks for pointing it out. By the way, when I perform this operations for one cell say C2 and then want to enhance it to the whole column, it seems that I can only do it by dragging the boundary of the cells with the mouse. Is this correct? I am asking since the original file in question has a column of 300 rows and using the mouse proved to be very very slow, but clean(C2:C300) did not work neither. Any comments? deletes that value to are trying to recover, it suggests that you want the value in C2 to be a CLEANed version of the value in C2 - which is a CLEANed version of what is in C2! The formula refers back to itself repeatedly, and the calculation can never end. If you were to use a formula to achieve what you need, you would have to put it into a spare column. You could choose to copy the results back into the original cells, but you would need to use Paste Special instead of ordinary paste and to deselect Formulae in the Paste Special dialogue so as to freeze the results. I trust this helps. Thanks indeed it does. - To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org For additional commands, e-mail: users-h...@openoffice.apache.org
Re: problem with import of excel file, cells contain new lines
El 19/02/15 a las 05:28, Uwe Brauer escribió: deletes that value to are trying to recover, it suggests that you want the value in C2 to be a CLEANed version of the value in C2 - which is a CLEANed version of what is in C2! The formula refers back to itself repeatedly, and the calculation can never end. If you were to use a formula to achieve what you need, you would have to put it into a spare column. You could choose to copy the results back into the original cells, but you would need to use Paste Special instead of ordinary paste and to deselect Formulae in the Paste Special dialogue so as to freeze the results. I trust this helps. Thanks indeed it does. - To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org For additional commands, e-mail: users-h...@openoffice.apache.org Maybe, as Andrew sugested in the list , the search and replace could be the solution. Try this. First, select the column C Call the search and replace option in the edit menu. In Search box fill with \n\t\t\t\t\t.. In Replace box left empty Click to open the more options mark option Only in current selection (I'm using a spanish versión, so I don't remember the correct words in english) mark oprtion Regular expression Click Replace all The pattern \n\t\t\t\t\t.. correspond to the weird caracters in cells of column C, if this pattern change, maybe this solution need a fix. Try it, and we'll see. SECOND VERSION.. Searching a little deep. I Found that you can use [:cntrl:] in search box, to erase all non printable characters. Doesn't matter if the pattern change. Maybe this is the easy way to solution Uwe's problem. Bye -- Manuel Andrés Ramírez P. Administrador Informático GNU/Linux Registered User:412887 Visite:www.getgnulinux.org/es/ Yo Tengo mi Office Legal y no pagué por su licencia El placer más noble es el júbilo de comprender (Leonardo Da Vinci) - To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org For additional commands, e-mail: users-h...@openoffice.apache.org
Re: problem with import of excel file, cells contain new lines
El 19/02/15 a las 11:18, Brian Barker escribió: At 10:53 19/02/2015 -0500, Manuel Andres Ramirez wrote: Searching a little deep. I Found that you can use [:cntrl:] in search box, to erase all non printable characters. Doesn't matter if the pattern change. Maybe this is the easy way to solution Uwe's problem. Nice idea. This removes the line break and the tab characters but sadly not the final space and non-breaking space. Brian Barker Brian, are you sure. I've tested and only remain de values inside the cells of column C. All nonprintable characters must be deleted. Are the regular expressión marked? in the search and replace dialog. -- Manuel Andrés Ramírez P. Administrador Informático GNU/Linux Registered User:412887 Visite:www.getgnulinux.org/es/ Yo Tengo mi Office Legal y no pagué por su licencia El placer más noble es el júbilo de comprender (Leonardo Da Vinci) - To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org For additional commands, e-mail: users-h...@openoffice.apache.org
Re: problem with import of excel file, cells contain new lines
Manuel == Manuel Andres Ramirez manuelandr...@aim.com writes: El 17/02/15 a las 10:06, Brian Barker escribió: At 09:19 17/02/2015 -0500, Manuel Andres Ramirez wrote: El 17/02/15 a las 06:00, Uwe Brauer escribió: I periodically receive excel files (generated by Office 2010 I think, but saved in the old binary format). Some of these columns cause me problems, since they seem to contain spaces and even newlines. I could correct that manually, but since the file contains hundred of lines this is cumbersome. So the question is whether there exist any formatting function which could resolve the issue. You can use TRIM() or LIMPIAR() in spanish function over the column C and correct the spaces or tabs. Thanks but. I run for example clean(C2) and it returns Err:522 C is the column with the problems smime.p7s Description: S/MIME cryptographic signature
Re: problem with import of excel file, cells contain new lines
At 08:51 18/02/2015 -0500, Manuel Andres Ramirez wrote: El 17/02/15 a las 10:06, Brian Barker escribió: At 09:19 17/02/2015 -0500, Manuel Andres Ramirez wrote: El 17/02/15 a las 06:00, Uwe Brauer escribió: I periodically receive excel files (generated by Office 2010 I think, but saved in the old binary format). Some of these columns cause me problems, since they seem to contain spaces and even newlines. I could correct that manually, but since the file contains hundred of lines this is cumbersome. So the question is whether there exist any formatting function which could resolve the issue. You can use TRIM() or LIMPIAR() in spanish function over the column C and correct the spaces or tabs. I tried TRIM(), but this seemed to do nothing in this case. It would not remove the tabs or line breaks or non-breaking spaces; it could remove the ordinary spaces, but only when they were trailing - so only if the non-breaking spaces had already been removed. Sorry, I tested with libreoffice in spanish and use LIMPIAR() function, so I translated wrongly as TRIM(), but now I realize that the correct function is CLEAN() Aha! You can see that my Spanish is non-existent! Try with CLEAN() over the Uwe attachment. In fact I had already tried using CLEAN() without success. I found that CLEAN() would remove the line break but only two tab characters at a time, so something like =CLEAN(CLEAN(CLEAN(C1))) was necessary even to remove all five tab characters. But that still left the space and the final non-breaking space. Applying TRIM() did not remove those, nor would VALUE() ignore them, so that transpired to be a dead end. Brian Barker - To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org For additional commands, e-mail: users-h...@openoffice.apache.org
Re: problem with import of excel file, cells contain new lines
At 18:29 18/02/2015 +0100, Uwe Brauer wrote: I run for example clean(C2) and it returns Err:522. C is the column with the problems That means that your formula is circular. I'm guessing that you have tried putting =CLEAN(C2) into C2 itself. Apart from the fact that this deletes that value to are trying to recover, it suggests that you want the value in C2 to be a CLEANed version of the value in C2 - which is a CLEANed version of what is in C2! The formula refers back to itself repeatedly, and the calculation can never end. If you were to use a formula to achieve what you need, you would have to put it into a spare column. You could choose to copy the results back into the original cells, but you would need to use Paste Special instead of ordinary paste and to deselect Formulae in the Paste Special dialogue so as to freeze the results. I trust this helps. Brian Barker - To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org For additional commands, e-mail: users-h...@openoffice.apache.org
Re: problem with import of excel file, cells contain new lines
El 18/02/15 a las 12:56, Brian Barker escribió: At 08:51 18/02/2015 -0500, Manuel Andres Ramirez wrote: El 17/02/15 a las 10:06, Brian Barker escribió: At 09:19 17/02/2015 -0500, Manuel Andres Ramirez wrote: El 17/02/15 a las 06:00, Uwe Brauer escribió: I periodically receive excel files (generated by Office 2010 I think, but saved in the old binary format). Some of these columns cause me problems, since they seem to contain spaces and even newlines. I could correct that manually, but since the file contains hundred of lines this is cumbersome. So the question is whether there exist any formatting function which could resolve the issue. You can use TRIM() or LIMPIAR() in spanish function over the column C and correct the spaces or tabs. I tried TRIM(), but this seemed to do nothing in this case. It would not remove the tabs or line breaks or non-breaking spaces; it could remove the ordinary spaces, but only when they were trailing - so only if the non-breaking spaces had already been removed. Sorry, I tested with libreoffice in spanish and use LIMPIAR() function, so I translated wrongly as TRIM(), but now I realize that the correct function is CLEAN() Aha! You can see that my Spanish is non-existent! Try with CLEAN() over the Uwe attachment. In fact I had already tried using CLEAN() without success. I found that CLEAN() would remove the line break but only two tab characters at a time, so something like =CLEAN(CLEAN(CLEAN(C1))) was necessary even to remove all five tab characters. But that still left the space and the final non-breaking space. Applying TRIM() did not remove those, nor would VALUE() ignore them, so that transpired to be a dead end. Brian Barker - To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org For additional commands, e-mail: users-h...@openoffice.apache.org Yes, you're right. Maybe, as Andrew sugested in the list , the search and replace could be the solution. Try this. First, select the column C Call the search and replace option in the edit menu. In Search box fill with \n\t\t\t\t\t.. In Replace box left empty Click to open the more options mark option Only in current selection (I'm using a spanish versión, so I don't remember the correct words in english) mark oprtion Regular expression Click Replace all The pattern \n\t\t\t\t\t.. correspond to the weird caracters in cells of column C, if this pattern change, maybe this solution need a fix. Try it, and we'll see. -- Manuel Andrés Ramírez P. Administrador Informático GNU/Linux Registered User:412887 Visite:www.getgnulinux.org/es/ Yo Tengo mi Office Legal y no pagué por su licencia El placer más noble es el júbilo de comprender (Leonardo Da Vinci) - To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org For additional commands, e-mail: users-h...@openoffice.apache.org
Re: problem with import of excel file, cells contain new lines
El 17/02/15 a las 10:06, Brian Barker escribió: At 09:19 17/02/2015 -0500, Manuel Andres Ramirez wrote: El 17/02/15 a las 06:00, Uwe Brauer escribió: I periodically receive excel files (generated by Office 2010 I think, but saved in the old binary format). Some of these columns cause me problems, since they seem to contain spaces and even newlines. I could correct that manually, but since the file contains hundred of lines this is cumbersome. So the question is whether there exist any formatting function which could resolve the issue. You can use TRIM() or LIMPIAR() in spanish function over the column C and correct the spaces or tabs. I tried TRIM(), but this seemed to do nothing in this case. It would not remove the tabs or line breaks or non-breaking spaces; it could remove the ordinary spaces, but only when they were trailing - so only if the non-breaking spaces had already been removed. Brian Barker - To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org For additional commands, e-mail: users-h...@openoffice.apache.org Brian, Sorry, I tested with libreoffice in spanish and use LIMPIAR() function, so I translated wrongly as TRIM(), but now I realize that the correct funtion is CLEAN() Try with CLEAN() over de Uwe attachment. Hava a nice day. -- Manuel Andrés Ramírez P. Administrador Informático GNU/Linux Registered User:412887 Visite:www.getgnulinux.org/es/ Yo Tengo mi Office Legal y no pagué por su licencia El placer más noble es el júbilo de comprender (Leonardo Da Vinci) - To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org For additional commands, e-mail: users-h...@openoffice.apache.org
Re: problem with import of excel file, cells contain new lines
At 12:00 17/02/2015 +0100, Uwe Brauer wrote: I periodically receive excel files (generated by Office 2010 I think, but saved in the old binary format). Some of these columns cause me problems, since they seem to contain spaces and even newlines. I could correct that manually, but since the file contains hundred of lines this is cumbersome. So the question is whether there exist any formatting function which could resolve the issue. I attach an (shorted) example of the problem and would appreciate any help. Using Linux and OO 4.1. Your rogue cells appear to contain the data (a digit, but as text), a line break, five tab characters, a space, and a non-breaking space - in that order. o Go to Edit | Find Replace (or Ctrl+F). o Click More Options and ensure Regular expressions is ticked. o You can remove the line breaks by replacing \n with nothing. o You can remove the tab characters by replacing \t with nothing. o You can remove non-breaking spaces by pasting one into the Search for field and replacing with nothing. To do this, one way is to select an unused cell, put the cursor into the Input Line, use Insert | Formatting Mark | Non-breaking space, press Enter or click the green tick mark, copy the cell, and paste into the Search for field. o You can remove spaces by replacing a space character with nothing. If you want to do this over multiple cells and want avoid removing significant spaces in other cells, you could do this by replacing $ (space-dollar, but without the quotes), which removes trailing spaces. You would need to do this after removing the non-breaking spaces, of course. I trust this helps. Brian Barker - To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org For additional commands, e-mail: users-h...@openoffice.apache.org
Re: problem with import of excel file, cells contain new lines
El 17/02/15 a las 06:00, Uwe Brauer escribió: Hello I periodically receive excel files (generated by Office 2010 I think, but saved in the old binary format). Some of these columns cause me problems, since they seem to contain spaces and even newlines. I could correct that manually, but since the file contains hundred of lines this is cumbersome. So the question is whether there exist any formating function which could resolve the issue. I attach an (shorted) example of the problem and would appreciate any help. Using Linux and OO 4.1. Regards Uwe Brauer Uwe and List friends, Good Morning. Looking your file, I think this information maybe is imported into excel from a text or csv file, and this process is not doing very well. You can use TRIM() or LIMPIAR() in spanish function over de column C and correct the spaces or tabs. Or maybe ask the informartion in text form, so you can import directly into libreoffice. Have a nice day. -- Manuel Andrés Ramírez P. Informatic Administrator GNU/Linux Registered User:412887 Visite:www.getgnulinux.org/es/ Yo Tengo mi Office Legal y no pagué por su licencia El placer más noble es el júbilo de comprender (Leonardo Da Vinci) - To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org For additional commands, e-mail: users-h...@openoffice.apache.org
Re: problem with import of excel file, cells contain new lines
At 09:19 17/02/2015 -0500, Manuel Andres Ramirez wrote: El 17/02/15 a las 06:00, Uwe Brauer escribió: I periodically receive excel files (generated by Office 2010 I think, but saved in the old binary format). Some of these columns cause me problems, since they seem to contain spaces and even newlines. I could correct that manually, but since the file contains hundred of lines this is cumbersome. So the question is whether there exist any formatting function which could resolve the issue. You can use TRIM() or LIMPIAR() in spanish function over the column C and correct the spaces or tabs. I tried TRIM(), but this seemed to do nothing in this case. It would not remove the tabs or line breaks or non-breaking spaces; it could remove the ordinary spaces, but only when they were trailing - so only if the non-breaking spaces had already been removed. Brian Barker - To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org For additional commands, e-mail: users-h...@openoffice.apache.org
