Calc: Search & Replace Within A Column

   Search and replace within a selected column produces incomplete and
inconsistent results. I need to learn how to do this correctly.

   Running -3.5.3_linux_x86.

   I mark a column by clicking on the column header cell and all rows in that
column are highlighted. Ctrl-H (or the menu equivalent) brings up the dialog
box. I enter 0.000 in the find widget and, for example, -0.005 in the
replace widget. The result of clicking the 'Replace All' button is a message
box telling me 'Search key not found.'

   This did work the first two times I tried it, but incompletely. Now it
won't work at all. I can scroll down the sheet and see entries with 0.000
but the function isn't working.

   Please clue me in how to successfully and completely run a find and
replace function. In all cases I'm now looking to replace all instances of
zero, but the replacement value varies by column. For another data set I
need to replace '<' with '-' so it's important that I can make these
changes.

TIA,

Rich


--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Hi Rich

This may be caused by at least two factors (maybe both of them):

1. Your sheet is displaying three decimal zeroes, but value of cell is
another. Check Format → Cells (or RMB → Format Cells) if they are formatted to
have three decimal zeroes.

2. Displayed zero is effect of computation of formula entered into cell. By
default, LO searches only formulas. To change this, in find and replace window
you have to click "more options" button and change "search in" to "values".

In both cases, you can check value of selected cell in formula field (right
above the sheet).

While you are in "More options", ensure that "current selection only" is
checked. Otherwise, you may unintentionally destroy your other data. Perhaps
creating backup copy of file before search-and-replace is good idea.
--
Best regards
Mirosław Zalewski

--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

On Fri, 25 May 2012, Mirosław Zalewski wrote:

> 1. Your sheet is displaying three decimal zeroes, but value of cell is
> another. Check Format → Cells (or RMB → Format Cells) if they are
> formatted to have three decimal zeroes.

Miroslaw,

   Each numeric column is formatted as numbers with no commas and 3 decimal
places.

> 2. Displayed zero is effect of computation of formula entered into cell.
> By default, LO searches only formulas. To change this, in find and replace
> window you have to click "more options" button and change "search in" to
> "values".

   There are no formulae in the sheet. All data values.

> In both cases, you can check value of selected cell in formula field (right
> above the sheet).

   This is interesting: cells that were changed show the formula of -0.0051
while the cells that were not changed show the formula of 0 but display
0.000. Yet, the cell format is the same: a number with 3 decimal places and
1 leading zero. What might cause this?

> While you are in "More options", ensure that "current selection only" is
> checked. Otherwise, you may unintentionally destroy your other data. Perhaps
> creating backup copy of file before search-and-replace is good idea.

   This was checked before.

Thanks,

Rich


--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Say X99 displays 0.005.
Turn on menu:View>Highlight Values [Ctrl+F8]. What is the font color of
X99? A blue color proves that X99 is a number. Text appears black.

Get some unused cell and enter
=X99=0.005
What is the result? Should be either TRUE or FALSE.

If FALSE, what is the result of
=X99-0.005
?


--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

On Fri, 25 May 2012, Andreas Säger wrote:

> Turn on menu:View>Highlight Values [Ctrl+F8]. What is the font color of X99?
> A blue color proves that X99 is a number. Text appears black.

   Other than column A (site names), every other cell is blue.

   I'm going to write an awk script that will change the zeros to the
appropriate reporting limit. That'll be quicker than my trying to understand
what's happening in Calc.

Thanks, Andreas,

Rich


--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

On 25/05/2012 at 15:14, Rich Shepard <[hidden email]> wrote:

>  I'm going to write an awk script that will change the zeros to the
> appropriate reporting limit. That'll be quicker than my trying to
> understand what's happening in Calc.

It may be.
Can you post your document somewhere on the web? I think that there are users
who are just curious what's so special about your data that neither of tips
have worked for you.
--
Best regards
Mirosław Zalewski

--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

On Fri, 25 May 2012, Mirosław Zalewski wrote:

> Can you post your document somewhere on the web? I think that there are
> users who are just curious what's so special about your data that neither
> of tips have worked for you.

Mirosław,

   It's client data so I cannot distribute it.

Rich



--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

On 25/05/2012 at 16:31, Rich Shepard <[hidden email]> wrote:

>   It's client data so I cannot distribute it.

I was afraid that's the case.

Sorry we could not solve your problem, then.
--
Best regards
Mirosław Zalewski

--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

On Fri, 25 May 2012, Mirosław Zalewski wrote:

> Sorry we could not solve your problem, then.

   That's OK. awk, sed, grep, cut, and sort do the job very quickly. :-)

Thanks to all for the suggesions,

Rich


--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

El 25/05/12 16:52, Rich Shepard escribió:
Maybe the mistake is in search for 0.000, it can't be found, because a
0.000 is always saved as 0, nonsignificant zeroes to the right/left of
decimal point are never saved.

Miguel Ángel

  * Inglés - detectado
  * Inglés
  * Español
  * Gallego
  * Italiano

  * Inglés
  * Español
  * Gallego
  * Italiano

  <javascript:void(0);>

--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Hi :)
Sometimes it's possible to replace all the confidential info in a file with random jumbles but in this case i think it would be
1.  difficult to do
2.  almost impossible to work out how to help

I think you solved this a gnu&linux way already anyway so it's all good now :)
Regards from
Tom :)


--- On Fri, 25/5/12, Mirosław Zalewski <[hidden email]> wrote:

From: Mirosław Zalewski <[hidden email]>
Subject: Re: [libreoffice-users] Re: Calc: Search & Replace Within A Column
To: [hidden email]
Date: Friday, 25 May, 2012, 15:18

On 25/05/2012 at 15:14, Rich Shepard <[hidden email]> wrote:

>  I'm going to write an awk script that will change the zeros to the
> appropriate reporting limit. That'll be quicker than my trying to
> understand what's happening in Calc.

It may be.
Can you post your document somewhere on the web? I think that there are users
who are just curious what's so special about your data that neither of tips
have worked for you.
--
Best regards
Mirosław Zalewski

--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Am 25.05.2012 16:31, Rich Shepard wrote:

Simply clear all text data so the remaining figures become meaningless.
I can not understand what is going on in your spreadsheet.


--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Am 25.05.2012 15:14, Rich Shepard wrote:
So you don't have any spreadsheet document. You are talking about a
plain text file. Right?
Loading plain text tables (csv) into spreadsheet is a very common,
nevertheless bad idea, particularly when you do not know anything about
spreadsheets.


--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

At 17:14 25/05/2012 +0200, MiguelAngel wrote:
>Maybe the mistake is in search for 0.000, it can't be found, because
>a 0.000 is always saved as 0, nonsignificant zeroes to the
>right/left of decimal point are never saved.

Computers store numbers in a fixed-length format - however-many
bytes.  So there are always the same number of binary digits stored -
which equates to a different but similarly (approximately) fixed
number of decimal digits stored (unless you choose multiple
precision, of course).  So surely what you may think of as
nonsignificant zeroes are not "never" but *always* stored?

Brian Barker


--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

El 26/05/12 12:41, Brian Barker escribió:
I think not a fixed format, but a fixed length with eight bytes used to
store the number in memory, as result fourteen significant numbers.

In any case the user can't find 0.0000 or 000.00, we can search for 0.

In content.xml file one of the files in .ods file, we can see the saved
values in office:value tag and the representation of the value in text:p
tag.

- <table:table table:name="Sheet1" table:style-name="ta1">
   <table:table-column table:style-name="co2"
table:default-cell-style-name="Default" />
- <table:table-row table:style-name="ro2">
- <table:table-cell table:style-name="ce1" office:value-type="float"
office:value="0">
   <text:p>000,000</text:p>
   </table:table-cell>
   </table:table-row>
- <table:table-row table:style-name="ro2">
- <table:table-cell office:value-type="float" office:value="12345">
   <text:p>12345</text:p>
   </table:table-cell>
   </table:table-row>
- <table:table-row table:style-name="ro2">
- <table:table-cell office:value-type="float" office:value="0.12345">
   <text:p>0,12</text:p>
   </table:table-cell>
   </table:table-row>
- <table:table-row table:style-name="ro2">
- <table:table-cell office:value-type="float" office:value="123.123">
   <text:p>123,12</text:p>
   </table:table-cell>
   </table:table-row>
- <table:table-row table:style-name="ro2">
- <table:table-cell table:style-name="ce2" office:value-type="float"
office:value="1.23456578901234E+015">
   <text:p>1,23E+015</text:p>
   </table:table-cell>
   </table:table-row>
- <table:table-row table:style-name="ro2">
- <table:table-cell table:style-name="ce3" office:value-type="float"
office:value="1.23456578901234E+015">
   <text:p>1234565789012340</text:p>
   </table:table-cell>
   </table:table-row>
   </table:table>


Miguel Ángel.

  * Inglés - detectado
  * Inglés
  * Español
  * Gallego
  * Italiano

  * Inglés
  * Español
  * Gallego
  * Italiano

  <javascript:void(0);>

--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

At 15:43 26/05/2012 +0200, Miguel Angel wrote:
There has to be a format in which the numbers are
stored, of course (however many bytes are used):
fourteen significant decimal digits maps to about
47 binary digits - just under six bytes.  The
rest of the space is used for an exponent and for
signs for both the exponent and the number
itself.  There are many possible formats, but
there has to be a fixed one in each computer or system.

>In content.xml file one of the files in .ods
>file, we can see the saved values in
>office:value tag and the representation of the value in text:p tag.

Thanks for this.  I confess I was thinking (as
you were above) of storage in the sense of
representation in the program itself - during
processing.  You are right, of course, that
storage in document files is in decimal, not
binary, and apparently omits insignificant trailing zeroes.

Brian Barker


--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted