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

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: 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