Date: prev next · Thread: first prev next last
2015 Archives by date, by thread · List index

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.

Brian Barker

To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be deleted


Privacy Policy | Impressum (Legal Info) | Copyright information: 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.