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: [email protected]
> > For additional commands, e-mail: [email protected]
>
> 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: [email protected]
For additional commands, e-mail: [email protected]
