At 11:58 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.
Your rogue cells (which I have examined by magic, despite your
attached file not making it through to the list!), 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.
To unsubscribe e-mail to: firstname.lastname@example.org
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
Impressum (Legal Info)
: Unless otherwise specified, all text and images
on this website are licensed under the
Creative Commons Attribution-Share Alike 3.0 License
This does not include the source code of LibreOffice, which is
licensed under the Mozilla Public License (MPLv2
"LibreOffice" and "The Document Foundation" are
registered trademarks of their corresponding registered owners or are
in actual use as trademarks in one or more countries. Their respective
logos and icons are also subject to international copyright laws. Use
thereof is explained in our trademark policy