At 12:00 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. Using Linux and OO 4.1.
Your rogue cells 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: users-unsubscr...@openoffice.apache.org
For additional commands, e-mail: users-h...@openoffice.apache.org
