> On Feb 17, 2015, at 6:16 AM, Brian Barker <[email protected]> wrote:
>
> 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 don’t know about the specific file you tried to send, but I
recently fixed a similar problem by turning off “Wrap text automatically”
(under Format -> Cells -> Allignment). You might check this, especially if you
want to keep blank spaces.
Hope this helps.
Spencer
>
> I trust this helps.
>
> Brian Barker
>
>
> --
> To unsubscribe e-mail to: [email protected]
> Problems?
> http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> 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
>
--
To unsubscribe e-mail to: [email protected]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
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
