Calc: Search & Replace Within A Column

Rich_Shepard · May 24, 2012, 9:02pm

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

Miroslaw_Zalewski · May 24, 2012, 11:25pm

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.

Rich_Shepard · May 25, 2012, 12:36am

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

Villeroy · May 25, 2012, 6:25am

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
?

Rich_Shepard · May 25, 2012, 1:14pm

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

Miroslaw_Zalewski · May 25, 2012, 2:18pm

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.

Rich_Shepard · May 25, 2012, 2:32pm

Mirosław,

It's client data so I cannot distribute it.

Rich

Miroslaw_Zalewski · May 25, 2012, 2:49pm

I was afraid that's the case.

Sorry we could not solve your problem, then.

Rich_Shepard · May 25, 2012, 2:52pm

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

Thanks to all for the suggesions,

Rich

mariosv · May 25, 2012, 3:14pm

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);>

Tom_Davies · May 25, 2012, 4:02pm

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

Villeroy · May 25, 2012, 5:20pm

Am 25.05.2012 16:31, Rich Shepard 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

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

Villeroy · May 25, 2012, 5:31pm

Am 25.05.2012 15:14, Rich Shepard 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

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.

Brian_Barker · May 26, 2012, 10:41am

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

mariosv · May 26, 2012, 1:43pm

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);>

Brian_Barker · May 26, 2012, 5:20pm

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?

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.

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