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

Reply via email to