problem with import of excel file, cells contain new lines

classic Classic list List threaded Threaded
3 messages Options
Uwe Brauer Uwe Brauer
Reply | Threaded
Open this post in threaded view
|

problem with import of excel file, cells contain new lines


Hello

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 formating function which
could resolve the issue.

I attach an (shorted) example of the problem and would appreciate any
help.

Regards

Uwe Brauer


--
To unsubscribe e-mail to: [hidden email]
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

Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|

Re: 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: [hidden email]
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

Spencer Graves-2 Spencer Graves-2
Reply | Threaded
Open this post in threaded view
|

Re: problem with import of excel file, cells contain new lines


> On Feb 17, 2015, at 6:16 AM, Brian Barker <[hidden email]> 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: [hidden email]
> 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: [hidden email]
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