On Sat, 2011-01-22 at 01:10 -0500, Joe Smith wrote: > 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.
> [...] > 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. Thanks, Joe. Your example worked. (And the explanation is great.) I still don't know why the other example didn't work. --Jean --------------------------------------------------------------------- To unsubscribe, e-mail: dev-unsubscr...@documentation.openoffice.org For additional commands, e-mail: dev-h...@documentation.openoffice.org