Re: [libreoffice-users] problem with import of excel file, cells contain new lines

> On Feb 17, 2015, at 6:16 AM, Brian Barker <b.m.barker@btinternet.com> 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: users+unsubscribe@global.libreoffice.org
> 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: users+unsubscribe@global.libreoffice.org
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