Question about regular expressions in Calc Find & Replace

classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

Question about regular expressions in Calc Find & Replace

Jean Hollis Weber-3
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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Question about regular expressions in Calc Find & Replace

Gareth McMullin
Hi Jean

Add numbers formatted as strings to your spreadsheet. For example:
in cell A1: '1
in cell A2: '2
in cell A3: =SUM(A1:A2)
A3 shows the value 0, because A1 and A2 are strings.  Doing the
find and replace as described reenters A1 and A2 as numbers, so A3
shows the value of 3.

I hope that helps.

Regards,
Gareth

On Sat, Jan 22, 2011 at 6:17 PM, Jean Hollis Weber <[hidden email]> 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: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>



--
Black Sphere Technologies Ltd.

Web: www.blacksphere.co.nz
Mobile: +64 27 777 2182
Tel: +64 9 478 8885
Skype: gareth.mcmullin
LinkedIn: http://nz.linkedin.com/in/gsmcmullin

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Question about regular expressions in Calc Find & Replace

Jean Hollis Weber-3
Doesn't work for me. Here are all the steps I did:

Format the cells as number, select the Regular expressions option,
select the cells A1, A2, A3, type .+ in the Search for box, & in the
Replace box, and click Replace.

No change to any cell.

What am I missing?

--Jean

On Sat, 2011-01-22 at 18:33 +1300, Gareth McMullin wrote:

> Hi Jean
>
> Add numbers formatted as strings to your spreadsheet. For example:
> in cell A1: '1
> in cell A2: '2
> in cell A3: =SUM(A1:A2)
> A3 shows the value 0, because A1 and A2 are strings.  Doing the
> find and replace as described reenters A1 and A2 as numbers, so A3
> shows the value of 3.
>
> I hope that helps.
>
> Regards,
> Gareth
>
> On Sat, Jan 22, 2011 at 6:17 PM, Jean Hollis Weber <[hidden email]> 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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Question about regular expressions in Calc Find & Replace

Jean Hollis Weber-3
In reply to this post by Gareth McMullin
And what do I pick in the "Search in" list: Formulas, Values, or Notes?


-------------previous note --------------
Doesn't work for me. Here are all the steps I did:

Format the cells as number, select the Regular expressions option,
select the cells A1, A2, A3, type .+ in the Search for box, & in the
Replace box, and click Replace.

No change to any cell.

What am I missing?

--Jean

On Sat, 2011-01-22 at 18:33 +1300, Gareth McMullin wrote:

> Hi Jean
>
> Add numbers formatted as strings to your spreadsheet. For example:
> in cell A1: '1
> in cell A2: '2
> in cell A3: =SUM(A1:A2)
> A3 shows the value 0, because A1 and A2 are strings.  Doing the
> find and replace as described reenters A1 and A2 as numbers, so A3
> shows the value of 3.
>
> I hope that helps.
>
> Regards,
> Gareth
>
> On Sat, Jan 22, 2011 at 6:17 PM, Jean Hollis Weber <[hidden email]> 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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Question about regular expressions in Calc Find & Replace

Joe Smith-4
In reply to this post by Jean Hollis Weber-3
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: [hidden email]
> For additional commands, e-mail: [hidden email]

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

Reply | Threaded
Open this post in threaded view
|

Re: Question about regular expressions in Calc Find & Replace

Joe Smith-4
On 01/22/2011 01:10 AM, Joe Smith wrote:
>...

Sorry, I replied before I saw Gareth's example.

Mine isn't substantially different, so you may get the same (lack of)
result. I'm not sure why you aren't seeing any effect.

<Joe

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Question about regular expressions in Calc Find & Replace

Andy Brown-5
In reply to this post by Jean Hollis Weber-3
On Fri Jan 21 2011 22:06:53 GMT-0800 (PST)  Jean Hollis Weber wrote:
> And what do I pick in the "Search in" list: Formulas, Values, or Notes?
>

Just found that it has to be Values.



---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Question about regular expressions in Calc Find & Replace

Gareth McMullin
In reply to this post by Jean Hollis Weber-3
On Sat, Jan 22, 2011 at 7:06 PM, Jean Hollis Weber <[hidden email]> wrote:

> And what do I pick in the "Search in" list: Formulas, Values, or Notes?
>
>
> -------------previous note --------------
> Doesn't work for me. Here are all the steps I did:
>
> Format the cells as number, select the Regular expressions option,
> select the cells A1, A2, A3, type .+ in the Search for box, & in the
> Replace box, and click Replace.
>
> No change to any cell.
>
> What am I missing?
>

I left "Search in" set to Formulas.  It worked for me.
You obviously need to select "Regular expressions".

If it's still not working I have no idea why.  Does "Find"
match the cells with contents?

Gareth

--
Black Sphere Technologies Ltd.

Web: www.blacksphere.co.nz
Mobile: +64 27 777 2182
Tel: +64 9 478 8885
Skype: gareth.mcmullin
LinkedIn: http://nz.linkedin.com/in/gsmcmullin

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Question about regular expressions in Calc Find & Replace

Jean Hollis Weber-3
In reply to this post by Joe Smith-4
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: [hidden email]
> > For additional commands, e-mail: [hidden email]
>
> 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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Question about regular expressions in Calc Find & Replace

Jean Hollis Weber-3
In reply to this post by Gareth McMullin
On Sat, 2011-01-22 at 19:28 +1300, Gareth McMullin wrote:

> On Sat, Jan 22, 2011 at 7:06 PM, Jean Hollis Weber <[hidden email]> wrote:
> >
> > Doesn't work for me. Here are all the steps I did:
> >
> > Format the cells as number, select the Regular expressions option,
> > select the cells A1, A2, A3, type .+ in the Search for box, & in the
> > Replace box, and click Replace.
> >
> > No change to any cell.
> >
> > What am I missing?
> >

> Does "Find" match the cells with contents?

I'm not sure I understand the question. Find didn't appear to do
anything when the info in the cells was '1, '2, etc. After running F&R,
the cells still contained '1, '2, etc. And the sum was still zero.

--Jean


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Question about regular expressions in Calc Find & Replace

Jean Hollis Weber-3
On Sat, Jan 22, 2011 at 10:47 PM, Jean Hollis Weber <[hidden email]> wrote:

> On Sat, 2011-01-22 at 19:28 +1300, Gareth McMullin wrote:
>> On Sat, Jan 22, 2011 at 7:06 PM, Jean Hollis Weber <[hidden email]> wrote:
>> >
>> > Doesn't work for me. Here are all the steps I did:
>> >
>> > Format the cells as number, select the Regular expressions option,
>> > select the cells A1, A2, A3, type .+ in the Search for box, & in the
>> > Replace box, and click Replace.
>> >
>> > No change to any cell.
>> >
>> > What am I missing?
>> >
>
>> Does "Find" match the cells with contents?
>
> I'm not sure I understand the question. Find didn't appear to do
> anything when the info in the cells was '1, '2, etc. After running F&R,
> the cells still contained '1, '2, etc. And the sum was still zero.
>

But Find *finds* the cells with contents, if I haven't selected them.

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]