On 01/22/2011 12:17 AM, Jean Hollis Weber wrote:
I found this page on the wiki,
http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Regular_Expressions_in_Calc#Regular_expressions_in_Calc_Find_.26_Replace
At the bottom of the page is this info:
"Find '.+' (or similar) and Replace with '&' effectively re-enters the
contents of cells. This can be used to 'strip' formatting automatically
applied by Calc (often needed to 'clean' data imported from the
clipboard or badly formatted files), for example, to convert text
strings consisting of digits, into actual numbers (the cells must first
be correctly formatted 'number'). The leading apostrophes, telling Calc
to treat the numbers as text, are removed."
I'm obviously missing something crucial about what to put into my test
spreadsheet and what to put into the Find and Replace boxes on the
dialog, because I get no change at all. Can someone please explain this
in different terms, or (better still) give a specific example? Thanks.
--Jean
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscr...@documentation.openoffice.org
For additional commands, e-mail: dev-h...@documentation.openoffice.org
Here's an example that, I think, demonstrates the problem and how this
operation works:
Copy the four numbers below to the clipboard.
100
400
2000
4
Paste them into cell A1 in a new sheet as unformatted text (Edit > Paste
Special > As Unformatted Text). You should get the "Import Text" dialog.
Click OK, to accept the defaults.
Now, calculate the total of the four values: enter the formula
=SUM(A1:A4) into any empty cell. Do you get the right answer?
No, you get a total of 2000 instead of the expected 2504.
The problem occurs because only the third value is imported as a numeric
value; the other three are imported as text because they have spaces
ahead of the digits. You can see this with View > Value Highlighting, or
if you click on the cells and check the value in the formula bar: the
text values are shown with a leading apostrophe, which Calc provides
automatically when a text value could be interpreted as a number.
How can the text values be converted to numeric values?
You can edit & re-enter each of the values--the leading spaces don't
matter when you enter a number manually. Remember to remove the leading
apostrophe!
Manually fixing the values isn't terrible for a few cells, but it very
quickly becomes impractical, especially because it's easy to make a
mistake and corrupt the data.
One trick to force a conversion /en masse/ is to use find & replace,
which causes Calc to re-interpret each cell after the replacement. As
long as the find & replace produces a valid numeric entry, the leading
spaces will be ignored, the text values will be converted to numeric
values, and the sum will be correct.
The find & replace pattern mentioned (".+", "&") is just a simple way to
"match anything and replace it with itself". That's an easy way to
perform a replacement on all the cells but without modifying any of the
values.
Another approach is to use Data > Text to Columns. This also forces Calc
to re-interpret the values, and it's easier for the user to carry out,
but it only works on one column at a time. Find & replace works for any
range you need.
<Joe
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscr...@documentation.openoffice.org
For additional commands, e-mail: dev-h...@documentation.openoffice.org
