You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openoffice.apache.org by David Deeks <pr...@gmail.com> on 2021/06/27 21:49:41 UTC
Mysterious ' in Calc
Hi there all you helpful people!
Can’t believe how much I’ve learned since ‘joining’ you lot (!), but am stuck with this one.
I have discovered that I have some part-columns of figures in Calc that do not sort properly, and have identified that, unlike other numbers in the same columns, they all appear in the ‘input line’ with a ‘ preceding them - whilst appearing in the body of the spreadsheet and in the ‘format’ window without it. I have extracted all the rogue ones into a separate spreadsheet in order to fiddle with them but have so far tried all different formats available without success.
See attached screenshot from my MacBook showing one of the fields highlighted, and how it appears in the ‘input line’ and cell format window.
Can anyone help get rid of the rogue ‘ ?
Thanks!
David Deeks
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Mysterious ' in Calc - WITH ATTACHMENT
Posted by David Deeks <pr...@gmail.com>.
Sorry, forgot attachment, here it is ….
> On 27 Jun 2021, at 22:49, David Deeks <pr...@gmail.com> wrote:
>
> Hi there all you helpful people!
>
> Can’t believe how much I’ve learned since ‘joining’ you lot (!), but am stuck with this one.
>
> I have discovered that I have some part-columns of figures in Calc that do not sort properly, and have identified that, unlike other numbers in the same columns, they all appear in the ‘input line’ with a ‘ preceding them - whilst appearing in the body of the spreadsheet and in the ‘format’ window without it. I have extracted all the rogue ones into a separate spreadsheet in order to fiddle with them but have so far tried all different formats available without success.
>
> See attached screenshot from my MacBook showing one of the fields highlighted, and how it appears in the ‘input line’ and cell format window.
>
> Can anyone help get rid of the rogue ‘ ?
>
> Thanks!
>
> David Deeks
Re: Mysterious ' in Calc
Posted by Brian Barker <b....@btinternet.com.INVALID>.
At 22:49 27/06/2021 +0100, David Deeks wrote:
>I have discovered that I have some part-columns of figures in Calc
>that do not sort properly, and have identified that, unlike other
>numbers in the same columns, they all appear in the "input line"
>with a ' preceding them - whilst appearing in the body of the
>spreadsheet and in the "format" window without it.
Those single quote marks are not really there, in that they do not
exist in the cell value. There is nothing mysterious about them: they
indicate that what may look like a number is actually a text value,
so 23, say, is actually the characters 2 and 3, not the number
twenty-three. The quote shows in the input line to help you. Note
that such text values will, by default, be left-aligned, unlike
genuine numbers, which are by default right-aligned. You should be
able to avoid this problem if you attend to the formatting of your
cell ranges (probably columns) before you enter values and take care
how you enter them.
>I have extracted all the rogue ones into a separate spreadsheet in
>order to fiddle with them but have so far tried all different
>formats available without success.
No need for that. There are various ways to repair values if what you
wanted was actually numbers. But here is a simple trick:
o Select the appropriate cells - possibly an entire column. (You can
include any genuine numbers without causing any problem.)
o Go to Data | Text to Columns... .
o Leave all options as default.
o OK.
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: Mysterious ' in Calc
Posted by David Deeks <pr...@gmail.com>.
Thanks for the very fast response Rory(!), but I already realised this and ‘Text’ is one of the formats I tried, with the same result? I am also mystified as to why it always shows the correct result in the ‘text to columns’ overlay screen, but not in the spreadsheet cells?
David
> On 28 Jun 2021, at 11:37, Rory O'Farrell <of...@iol.ie> wrote:
>
> On Mon, 28 Jun 2021 11:30:28 +0100
> David Deeks <prof.david.deeks@gmail.com <ma...@gmail.com>> wrote:
>
>> Thanks very much Brian - almost completely sorted!
>>
>> I have a few exceptions where the contents of the field are not simple numbers. They indicate a small range thus e.g. 2-3, 3-4, or an either/or thus e.g. 2/3, 3/4.
>>
>> In both cases they show the correct result 2-3 or 2/3 in the text to columns overlay screen, but now give these results in the spreadsheet?
>> 2-3 or 2/3:
>> 442573-4 or 3/4:
>> 44289
>>
>> Is there a cell format I can use that would work correctly for these? I feel as though I have tried everything!
>>
>> Thanks again
>>
>> David
>
>
> A range 2-3 or 2/3 is not a number for computation, so use text format. Similarly for numbers such as ZIP codes, phone numbers, credit card numbers - one does not calculate with these, so they should be formatted as text.
>
> RoryOF
>
>>
>>> On 27 Jun 2021, at 23:46, Brian Barker <b....@btinternet.com.INVALID> wrote:
>>>
>>> At 22:49 27/06/2021 +0100, David Deeks wrote:
>>>> I have discovered that I have some part-columns of figures in Calc that do not sort properly, and have identified that, unlike other numbers in the same columns, they all appear in the "input line" with a ' preceding them - whilst appearing in the body of the spreadsheet and in the "format" window without it.
>>>
>>> Those single quote marks are not really there, in that they do not exist in the cell value. There is nothing mysterious about them: they indicate that what may look like a number is actually a text value, so 23, say, is actually the characters 2 and 3, not the number twenty-three. The quote shows in the input line to help you. Note that such text values will, by default, be left-aligned, unlike genuine numbers, which are by default right-aligned. You should be able to avoid this problem if you attend to the formatting of your cell ranges (probably columns) before you enter values and take care how you enter them.
>>>
>>>> I have extracted all the rogue ones into a separate spreadsheet in order to fiddle with them but have so far tried all different formats available without success.
>>>
>>> No need for that. There are various ways to repair values if what you wanted was actually numbers. But here is a simple trick:
>>> o Select the appropriate cells - possibly an entire column. (You can include any genuine numbers without causing any problem.)
>>> o Go to Data | Text to Columns... .
>>> o Leave all options as default.
>>> o OK.
>>>
>>> I trust this helps.
>>>
>>> Brian Barker
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
>>> For additional commands, e-mail: users-help@openoffice.apache.org
>>>
>>
>
>
> --
> Rory O'Farrell <ofarrwrk@iol.ie <ma...@iol.ie>>
Re: Mysterious ' in Calc
Posted by Rory O'Farrell <of...@iol.ie>.
On Mon, 28 Jun 2021 11:30:28 +0100
David Deeks <pr...@gmail.com> wrote:
> Thanks very much Brian - almost completely sorted!
>
> I have a few exceptions where the contents of the field are not simple numbers. They indicate a small range thus e.g. 2-3, 3-4, or an either/or thus e.g. 2/3, 3/4.
>
> In both cases they show the correct result 2-3 or 2/3 in the text to columns overlay screen, but now give these results in the spreadsheet?
> 2-3 or 2/3:
> 442573-4 or 3/4:
> 44289
>
> Is there a cell format I can use that would work correctly for these? I feel as though I have tried everything!
>
> Thanks again
>
> David
A range 2-3 or 2/3 is not a number for computation, so use text format. Similarly for numbers such as ZIP codes, phone numbers, credit card numbers - one does not calculate with these, so they should be formatted as text.
RoryOF
>
> > On 27 Jun 2021, at 23:46, Brian Barker <b....@btinternet.com.INVALID> wrote:
> >
> > At 22:49 27/06/2021 +0100, David Deeks wrote:
> >> I have discovered that I have some part-columns of figures in Calc that do not sort properly, and have identified that, unlike other numbers in the same columns, they all appear in the "input line" with a ' preceding them - whilst appearing in the body of the spreadsheet and in the "format" window without it.
> >
> > Those single quote marks are not really there, in that they do not exist in the cell value. There is nothing mysterious about them: they indicate that what may look like a number is actually a text value, so 23, say, is actually the characters 2 and 3, not the number twenty-three. The quote shows in the input line to help you. Note that such text values will, by default, be left-aligned, unlike genuine numbers, which are by default right-aligned. You should be able to avoid this problem if you attend to the formatting of your cell ranges (probably columns) before you enter values and take care how you enter them.
> >
> >> I have extracted all the rogue ones into a separate spreadsheet in order to fiddle with them but have so far tried all different formats available without success.
> >
> > No need for that. There are various ways to repair values if what you wanted was actually numbers. But here is a simple trick:
> > o Select the appropriate cells - possibly an entire column. (You can include any genuine numbers without causing any problem.)
> > o Go to Data | Text to Columns... .
> > o Leave all options as default.
> > o OK.
> >
> > I trust this helps.
> >
> > Brian Barker
> >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
> > For additional commands, e-mail: users-help@openoffice.apache.org
> >
>
--
Rory O'Farrell <of...@iol.ie>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: Mysterious ' in Calc
Posted by Brian Barker <b....@btinternet.com.INVALID>.
At 11:30 28/06/2021 +0100, David Deeks wrote:
>Thanks very much Brian - almost completely sorted!
>
>I have a few exceptions where the contents of the field are not
>simple numbers. They indicate a small range thus e.g. 2-3, 3-4, or
>an either/or thus e.g. 2/3, 3/4.
>
>In both cases they show the correct result 2-3 or 2/3 in the text to
>columns overlay screen, but now give these results in the spreadsheet?
>2-3 or 2/3:
>442573-4 or 3/4:
>44289
Have you missed out the exposition you were intending to give here?
>Is there a cell format I can use that would work correctly for these?
There is a sense that you are expecting magic here: you want a format
that will "work correctly" without explaining what you mean by that.
Those rogue values that you are describing as "not simple numbers"
are indeed not numbers at all. Remember also that formatting affects
the display of values in cells - *not* the values themselves. If you
think "2-3" is a number, please tell me its square root. Or, in a
numerically sorted list, where does it come relative to "2.1" or to
"2/3" or to "1-2.5"?
If you want to sort values that are not numbers, you can sort them
all as text. That means that "2.1" sorts between "2-3" and "2/3" -
oh, and "3" sorts after "20" and "200", because "3" sorts after "2".
Is that what you want?
What do you consider "correct"?
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: Mysterious ' in Calc
Posted by Brian Barker <b....@btinternet.com.INVALID>.
At 15:35 28/06/2021 +0100, David Deeks wrote:
>Thanks for this Brian - feeling suitably
>admonished here for not being specific enough
>particularly as I spent my working life designing computer business systems!
Oh, I was not trying to be critical.
>I don't require them to be numbers per se as
>explained in my first email I need to be able
>to sort them, but should have made it clear that
>I do not need them to be actual numerics for the purposes of calculations.
Good. But "2-3" doesn't really sort anywhere
between numbers. And if you sort text, "3" comes
*after* "20". So which do you want?
>So having followed your original advice with a
>successful conclusion in those cases where there
>are just single values in the cells, I should
>have explained my follow-up query as ...
>
In both cases they show the required result
>e.g. 2-3 or 2/3 etc in the Text to Columns
>overlay screen, but now give these results, for example, in the spreadsheet?
>2-3 or 2/3 now display in cells as:
>44257
>3-4 or 3/4 now display in cells as:
>44289
>etc.
>
>Whether the format is text or number, I seem to get the same result.
Apologies that this statement was rather mangled
in transit in your previous message, so I
completely misunderstood it. But it's easy to
explain. The Text to Columns facility causes
OpenOffice to reinterpret the values in the
cells: that is how the *text* "23" becomes the
number 23. And the same thing happens to your
examples with hyphens or slashes. If the numbers
around those characters are appropriate, such
text strings typed into a cell would be
interpreted as dates and displayed as such. What
has happened here is that your values are dates
but, because your cells are formatted as number,
what is displayed is the number actually stored
in a cell for a date value - the number of days
and fractions of a day since the origin date. If
you format these cells as Date, you will see that
44257 (for 2-3 or 2/3) is 2 March 2021 and 44289
(for 3-4 or 3/4) is 3 April 2021. (This follows
the UK interpretation of shorthand dates, where
it appears you may be, which is different from the US one, of course.)
>My queries I suppose have therefore become twofold ..
>1 Why my required results show in the Text to
>Columns overlay screen, but then appear otherwise on the spreadsheet
See above.
>2. Is there a cell format I can use that would work as required for the above?
It's not a question of formatting but of the cell
*values* you have. And Text to Columns, though it
will sort your real text numbers to numeric
values, indeed messes up values that look like
shorthand dates. You may have to handle some of
this manually. But I still don't know why you
want your text values converted to numbers, as
you cannot sensibly sort "2-3" within numbers.
Where exactly do you expect it to appear?
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: [No longer] Mysterious ' in Calc
Posted by Brian Barker <b....@btinternet.com.INVALID>.
At 22:55 28/06/2021 +0100, David Deeks wrote:
>Thanks very much for your explanation re the Text to Columns facility Brian.
No probs!
>To answer your question
>For many years I have done reviews for a music
>magazine and the values are quality ratings (1-5
>"stars") for vinyl records and cds - three
>columns denoting Music Quality, Sound Quality,
>and (for vinyl) Background Noise i.e.
>clicks/pops. An entry such as 2-3 means that the
>tracks across the release vary from 2-3. An
>entry such as 2/3 means that the first side of a
>vinyl is rated an overall 2, the second side 3.
Oh, I think everyone was clear about what your codes meant.
>The spreadsheet is many years old, passed on to
>me and perhaps originating from Microsoft Excel.
>Some of the entries in the sorted column have a
>preceding ' whereas others do not. I have never
>needed to "sort" the reviewed recordings by
>quality rating before, but have recently needed
>to do so, hence discovering the resulting
>problem. All of the entries with a preceding '
>sort "successfully" i.e. like this e.g. 2, then
>2/3, 2-3, 3, 3/4 etc. All of the entries without
>the ' do so too, it's just that they don't mix!
As previously explained, there are no single
quotes in your cells: they show up in the Input
Line just as an indication that those values are
text values in cells with numeric, not text,
formatting. If - as is clear - you need values
with hyphens and slashes that are not numbers,
then you need your values all to be text. You -
or in this case the person who created the
spreadsheet - should have made the decision in
designing the spreadsheet to format the relevant
cells as Text and to enter values as text.
>For my immediate requirement I'll see to these
>rogue fields by manually inserting the rows into
>the required slots as you suggest. For future
>use I'll have a go at modifying the spreadsheet in some way.
It's easier than that:
o Select the relevant cell range - probably an entire column.
o Go to Format | Cells... | Numbers and select Text under Category.
o Note that changing the format will *not* change
any values, so this doesn't solve existing problems!
o Select the relevant cell range.
o Go to Data | Text to Columns... .
o Under Fields, click the word "Standard" at the head of the column.
o Against "Column type", select Text from the drop-down menu.
o OK.
Now all your values should be text values
formatted as text, and they will sort as text.
This means that "2-3" and "2/3" will end up between 2 and 3, as you require.
There is one remaining problem that I mentioned
before. Because "2" as text comes before "3" as
text, anything starting "2" will sort - as text -
before anything starting "3". That means that
twenty ("20") and even two million ("2000000")
will both sort before "3". But since your values
do not exceed 5 - in other words they are all
single digits - this problem will not show for you.
>While I have your attention(!), I'll push my
>luck with one more query! You helped me a while
>back re re-sizing of rows. My query now is, once
>these have been re-sized to increase their
>height so that the values are fully displayed
>(whether by double clicking, using 'optimal
>height' or manually) why is it that after
>saving/closing the spreadsheet they re-appear as
>the wrong size again, and is there a way of more permanently re-sizing?
That's not my experience or, I imagine, anyone
else's. I don't know exactly what you can be
doing to cause this. Oh, you are not saving your
document in the foreign Microsoft .xls format,
are you? I'm not sure that would be the culprit,
but it can cause problems. As Joyce Grenfell said
to George, "Don't do that". Use OpenOffice's
native Open Document Format formats - here .ods.
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: [No longer] Mysterious ' in Calc
Posted by David Deeks <pr...@gmail.com>.
Thanks again Brian. I’ll change the columns to text as you suggest.
The spreadsheet is saved as .ods. Perhaps as I think it originated as a .xls this explains the problem of rows reverting to wrong size. At some point, if ‘life’ doesn’t get in the way, I may have a go at re-building a .ods version from scratch!
All best
David
> On 28 Jun 2021, at 23:36, Brian Barker <b....@btinternet.com.INVALID> wrote:
>
> At 22:55 28/06/2021 +0100, David Deeks wrote:
>> Thanks very much for your explanation re the Text to Columns facility Brian.
>
> No probs!
>
>> To answer your question
>
>> For many years I have done reviews for a music magazine and the values are quality ratings (1-5 "stars") for vinyl records and cds - three columns denoting Music Quality, Sound Quality, and (for vinyl) Background Noise i.e. clicks/pops. An entry such as 2-3 means that the tracks across the release vary from 2-3. An entry such as 2/3 means that the first side of a vinyl is rated an overall 2, the second side 3.
>
> Oh, I think everyone was clear about what your codes meant.
>
>> The spreadsheet is many years old, passed on to me and perhaps originating from Microsoft Excel. Some of the entries in the sorted column have a preceding ' whereas others do not. I have never needed to "sort" the reviewed recordings by quality rating before, but have recently needed to do so, hence discovering the resulting problem. All of the entries with a preceding ' sort "successfully" i.e. like this e.g. 2, then 2/3, 2-3, 3, 3/4 etc. All of the entries without the ' do so too, it's just that they don't mix!
>
> As previously explained, there are no single quotes in your cells: they show up in the Input Line just as an indication that those values are text values in cells with numeric, not text, formatting. If - as is clear - you need values with hyphens and slashes that are not numbers, then you need your values all to be text. You - or in this case the person who created the spreadsheet - should have made the decision in designing the spreadsheet to format the relevant cells as Text and to enter values as text.
>
>> For my immediate requirement I'll see to these rogue fields by manually inserting the rows into the required slots as you suggest. For future use I'll have a go at modifying the spreadsheet in some way.
>
> It's easier than that:
> o Select the relevant cell range - probably an entire column.
> o Go to Format | Cells... | Numbers and select Text under Category.
> o Note that changing the format will *not* change any values, so this doesn't solve existing problems!
> o Select the relevant cell range.
> o Go to Data | Text to Columns... .
> o Under Fields, click the word "Standard" at the head of the column.
> o Against "Column type", select Text from the drop-down menu.
> o OK.
>
> Now all your values should be text values formatted as text, and they will sort as text. This means that "2-3" and "2/3" will end up between 2 and 3, as you require.
>
> There is one remaining problem that I mentioned before. Because "2" as text comes before "3" as text, anything starting "2" will sort - as text - before anything starting "3". That means that twenty ("20") and even two million ("2000000") will both sort before "3". But since your values do not exceed 5 - in other words they are all single digits - this problem will not show for you.
>
>> While I have your attention(!), I'll push my luck with one more query! You helped me a while back re re-sizing of rows. My query now is, once these have been re-sized to increase their height so that the values are fully displayed (whether by double clicking, using 'optimal height' or manually) why is it that after saving/closing the spreadsheet they re-appear as the wrong size again, and is there a way of more permanently re-sizing?
>
> That's not my experience or, I imagine, anyone else's. I don't know exactly what you can be doing to cause this. Oh, you are not saving your document in the foreign Microsoft .xls format, are you? I'm not sure that would be the culprit, but it can cause problems. As Joyce Grenfell said to George, "Don't do that". Use OpenOffice's native Open Document Format formats - here .ods.
>
> I trust this helps.
>
> Brian Barker
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
> For additional commands, e-mail: users-help@openoffice.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: [No longer] Mysterious ' in Calc
Posted by Girvin Herr <gh...@fastmail.com>.
On 6/28/21 4:55 PM, Dave Fisher wrote:
>
> Sent from my iPhone
>
>> On Jun 28, 2021, at 4:45 PM, Girvin Herr <gh...@fastmail.com> wrote:
>>
>>
>>> On 6/28/21 3:36 PM, Brian Barker wrote:
>>> At 22:55 28/06/2021 +0100, David Deeks wrote:
>>>> Thanks very much for your explanation re the Text to Columns facility Brian.
>>> <snip>
>>> That's not my experience or, I imagine, anyone else's.
>> Brian et al.,
>>
>> Your imagination is wrong. I have seen it. I just saw it on one of my spreadsheets. In my case it is sensitive to the cell's line length. If the text ends close to the right side of the cell, but not quite close enough to wrap, the row will be double height (wasted white space). I deleted one character and the row jumped to the proper single height. When I put the character back again, the row stayed the proper height. I am pretty sure if I close the file and reopen it again, I will have the double height back. Confirmed - I did a save and "File" -> "Reload" and the row is back to double-height.
>>
>> I have also seen the opposite - row text that is definitely two lines, but the row is single height, making the text cropped and unreadable until I double-click on the row bottom, then it will pop up to the proper double height. I have no idea what is causing it. It seems somewhat random sometimes, but random action does not apply to a computer program. This "feature" has not been fatal to my work, so I just work-around it.
> What you describe is a font metrics problem where the metrics used don’t match the font used. Can you describe your OS version, OpenOffice version, font including style and size selected, and if that font is present in your system.
>
> Regards,
> Dave
>
Dave,
Oops. I should have provided that:
OpenOffice 4.1.10, AOO4110m2(Build:9807) - Rev. b1cdbd2c1b
2021-04-19 18:17 - Linux i686
on Slackware 14.2 Linux K4.4.261.
In this case I am using "Liberation Sans" 8pt "Regular" with no effects
and "Wrap text automatically" checked in the alignment dialog. The font
is on my system -
liberation-fonts-ttf-1.07.4-noarch-1 :
...
usr/share/fonts/TTF/LiberationSans-Bold.ttf
usr/share/fonts/TTF/LiberationSans-BoldItalic.ttf
usr/share/fonts/TTF/LiberationSans-Italic.ttf
usr/share/fonts/TTF/LiberationSans-Regular.ttf
usr/share/fonts/TTF/LiberationSansNarrow-Bold.ttf
usr/share/fonts/TTF/LiberationSansNarrow-BoldItalic.ttf
usr/share/fonts/TTF/LiberationSansNarrow-Italic.ttf
usr/share/fonts/TTF/LiberationSansNarrow-Regular.ttf
...
HTH
Girvin
>>> I don't know exactly what you can be doing to cause this. Oh, you are not saving your document in the foreign Microsoft .xls format, are you? I'm not sure that would be the culprit, but it can cause problems. As Joyce Grenfell said to George, "Don't do that". Use OpenOffice's native Open Document Format formats - here .ods.
>>>
>> I _never_ save in M$ format.
>>> I trust this helps.
>>>
>>> Brian Barker
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
>>> For additional commands, e-mail: users-help@openoffice.apache.org
>>>
>> Girvin Herr
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
> For additional commands, e-mail: users-help@openoffice.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: [No longer] Mysterious ' in Calc
Posted by Dave Fisher <wa...@comcast.net>.
Sent from my iPhone
> On Jun 28, 2021, at 4:45 PM, Girvin Herr <gh...@fastmail.com> wrote:
>
>
>> On 6/28/21 3:36 PM, Brian Barker wrote:
>> At 22:55 28/06/2021 +0100, David Deeks wrote:
>>> Thanks very much for your explanation re the Text to Columns facility Brian.
>>
>> <snip>
>
>> That's not my experience or, I imagine, anyone else's.
>
> Brian et al.,
>
> Your imagination is wrong. I have seen it. I just saw it on one of my spreadsheets. In my case it is sensitive to the cell's line length. If the text ends close to the right side of the cell, but not quite close enough to wrap, the row will be double height (wasted white space). I deleted one character and the row jumped to the proper single height. When I put the character back again, the row stayed the proper height. I am pretty sure if I close the file and reopen it again, I will have the double height back. Confirmed - I did a save and "File" -> "Reload" and the row is back to double-height.
>
> I have also seen the opposite - row text that is definitely two lines, but the row is single height, making the text cropped and unreadable until I double-click on the row bottom, then it will pop up to the proper double height. I have no idea what is causing it. It seems somewhat random sometimes, but random action does not apply to a computer program. This "feature" has not been fatal to my work, so I just work-around it.
What you describe is a font metrics problem where the metrics used don’t match the font used. Can you describe your OS version, OpenOffice version, font including style and size selected, and if that font is present in your system.
Regards,
Dave
>
>> I don't know exactly what you can be doing to cause this. Oh, you are not saving your document in the foreign Microsoft .xls format, are you? I'm not sure that would be the culprit, but it can cause problems. As Joyce Grenfell said to George, "Don't do that". Use OpenOffice's native Open Document Format formats - here .ods.
>>
> I _never_ save in M$ format.
>> I trust this helps.
>>
>> Brian Barker
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
>> For additional commands, e-mail: users-help@openoffice.apache.org
>>
> Girvin Herr
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: [No longer] Mysterious ' in Calc
Posted by Girvin Herr <gh...@fastmail.com>.
On 6/28/21 3:36 PM, Brian Barker wrote:
> At 22:55 28/06/2021 +0100, David Deeks wrote:
>> Thanks very much for your explanation re the Text to Columns facility
>> Brian.
>
> <snip>
> That's not my experience or, I imagine, anyone else's.
Brian et al.,
Your imagination is wrong. I have seen it. I just saw it on one of my
spreadsheets. In my case it is sensitive to the cell's line length. If
the text ends close to the right side of the cell, but not quite close
enough to wrap, the row will be double height (wasted white space). I
deleted one character and the row jumped to the proper single height.
When I put the character back again, the row stayed the proper height. I
am pretty sure if I close the file and reopen it again, I will have the
double height back. Confirmed - I did a save and "File" -> "Reload" and
the row is back to double-height.
I have also seen the opposite - row text that is definitely two lines,
but the row is single height, making the text cropped and unreadable
until I double-click on the row bottom, then it will pop up to the
proper double height. I have no idea what is causing it. It seems
somewhat random sometimes, but random action does not apply to a
computer program. This "feature" has not been fatal to my work, so I
just work-around it.
> I don't know exactly what you can be doing to cause this. Oh, you are
> not saving your document in the foreign Microsoft .xls format, are
> you? I'm not sure that would be the culprit, but it can cause
> problems. As Joyce Grenfell said to George, "Don't do that". Use
> OpenOffice's native Open Document Format formats - here .ods.
>
I _never_ save in M$ format.
> I trust this helps.
>
> Brian Barker
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
> For additional commands, e-mail: users-help@openoffice.apache.org
>
Girvin Herr
Re: Mysterious ' in Calc
Posted by David Deeks <pr...@gmail.com>.
Thanks very much for your explanation re the Text to Columns facility Brian.
To answer your question …
For many years I have done reviews for a music magazine and the values are quality ratings (1-5 ’stars’) for vinyl records and cds - three columns denoting Music Quality, Sound Quality, and (for vinyl) Background Noise i.e. clicks/pops. An entry such as 2-3 means that the tracks across the release vary from 2-3. An entry such as 2/3 means that the first side of a vinyl is rated an overall 2, the second side 3.
The spreadsheet is many years old, passed on to me and perhaps originating from Microsoft Excel. Some of the entries in the sorted column have a preceding ‘ whereas others do not. I have never needed to ‘sort’ the reviewed recordings by quality rating before, but have recently needed to do so, hence discovering the resulting problem. All of the entries with a preceding ‘ sort ’successfully’ i.e. like this e.g. 2, then 2/3, 2-3, 3, 3/4 etc. All of the entries without the ‘ do so too, it’s just that they don’t mix!
For my immediate requirement I’ll see to these rogue fields by manually inserting the rows into the required slots as you suggest. For future use I’ll have a go at modifying the spreadsheet in some way.
While I have your attention(!), I’ll push my luck with one more query! You helped me a while back re re-sizing of rows. My query now is, once these have been re-sized to increase their height so that the values are fully displayed (whether by double clicking, using ‘optimal height' or manually) why is it that after saving/closing the spreadsheet they re-appear as the wrong size again, and is there a way of more permanently re-sizing?
Thanks again
All best
David
> On 28 Jun 2021, at 16:24, Brian Barker <b....@btinternet.com.INVALID> wrote:
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: Mysterious ' in Calc
Posted by David Deeks <pr...@gmail.com>.
Thanks for this Brian - feeling suitably admonished here for not being specific enough – particularly as I spent my working life designing computer business systems!
I don’t require them to be numbers per se – as explained in my first email I need to be able to sort them, but should have made it clear that I do not need them to be actual numerics for the purposes of calculations.
So having followed your original advice with a successful conclusion in those cases where there are just single values in the cells, I should have explained my follow-up query as ...
… In both cases they show the required result e.g. 2-3 or 2/3 etc in the Text to Columns overlay screen, but now give these results, for example, in the spreadsheet?
2-3 or 2/3 now display in cells as:
44257
3-4 or 3/4 now display in cells as:
44289
etc.
Whether the format is text or number, I seem to get the same result.
My queries I suppose have therefore become twofold ..
1 Why my required results show in the Text to Columns overlay screen, but then appear otherwise on the spreadsheet
2. Is there a cell format I can use that would work as required for the above?
Nervously about to hit 'send’! ....
David
> On 28 Jun 2021, at 13:49, Brian Barker <b....@btinternet.com.INVALID> wrote:
>
> At 11:30 28/06/2021 +0100, David Deeks wrote:
>> Thanks very much Brian - almost completely sorted!
>>
>> I have a few exceptions where the contents of the field are not simple numbers. They indicate a small range thus e.g. 2-3, 3-4, or an either/or thus e.g. 2/3, 3/4.
>>
>> In both cases they show the correct result 2-3 or 2/3 in the text to columns overlay screen, but now give these results in the spreadsheet?
>> 2-3 or 2/3:
>> 442573-4 or 3/4:
>> 44289
>
> Have you missed out the exposition you were intending to give here?
>
>> Is there a cell format I can use that would work correctly for these?
>
> There is a sense that you are expecting magic here: you want a format that will "work correctly" without explaining what you mean by that. Those rogue values that you are describing as "not simple numbers" are indeed not numbers at all. Remember also that formatting affects the display of values in cells - *not* the values themselves. If you think "2-3" is a number, please tell me its square root. Or, in a numerically sorted list, where does it come relative to "2.1" or to "2/3" or to "1-2.5"?
>
> If you want to sort values that are not numbers, you can sort them all as text. That means that "2.1" sorts between "2-3" and "2/3" - oh, and "3" sorts after "20" and "200", because "3" sorts after "2". Is that what you want?
>
> What do you consider "correct"?
>
> I trust this helps.
>
> Brian Barker
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
> For additional commands, e-mail: users-help@openoffice.apache.org
>
Re: Mysterious ' in Calc
Posted by David Deeks <pr...@gmail.com>.
Thanks very much Brian - almost completely sorted!
I have a few exceptions where the contents of the field are not simple numbers. They indicate a small range thus e.g. 2-3, 3-4, or an either/or thus e.g. 2/3, 3/4.
In both cases they show the correct result 2-3 or 2/3 in the text to columns overlay screen, but now give these results in the spreadsheet?
2-3 or 2/3:
442573-4 or 3/4:
44289
Is there a cell format I can use that would work correctly for these? I feel as though I have tried everything!
Thanks again
David
> On 27 Jun 2021, at 23:46, Brian Barker <b....@btinternet.com.INVALID> wrote:
>
> At 22:49 27/06/2021 +0100, David Deeks wrote:
>> I have discovered that I have some part-columns of figures in Calc that do not sort properly, and have identified that, unlike other numbers in the same columns, they all appear in the "input line" with a ' preceding them - whilst appearing in the body of the spreadsheet and in the "format" window without it.
>
> Those single quote marks are not really there, in that they do not exist in the cell value. There is nothing mysterious about them: they indicate that what may look like a number is actually a text value, so 23, say, is actually the characters 2 and 3, not the number twenty-three. The quote shows in the input line to help you. Note that such text values will, by default, be left-aligned, unlike genuine numbers, which are by default right-aligned. You should be able to avoid this problem if you attend to the formatting of your cell ranges (probably columns) before you enter values and take care how you enter them.
>
>> I have extracted all the rogue ones into a separate spreadsheet in order to fiddle with them but have so far tried all different formats available without success.
>
> No need for that. There are various ways to repair values if what you wanted was actually numbers. But here is a simple trick:
> o Select the appropriate cells - possibly an entire column. (You can include any genuine numbers without causing any problem.)
> o Go to Data | Text to Columns... .
> o Leave all options as default.
> o OK.
>
> I trust this helps.
>
> Brian Barker
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
> For additional commands, e-mail: users-help@openoffice.apache.org
>
Re: Mysterious ' in Calc
Posted by Brian Barker <b....@btinternet.com.INVALID>.
At 17:22 27/06/2021 -0700, Chuck Spalding wrote:
>On Sun, Jun 27, 2021 at 3:46 PM Brian Barker wrote:
>>At 22:49 27/06/2021 +0100, David Deeks wrote:
>>>I have discovered that I have some part-columns of figures in Calc
>>>that do not sort properly, and have identified that, unlike other
>>>numbers in the same columns, they all appear in the "input line"
>>>with a ' preceding them - whilst appearing in the body of the
>>>spreadsheet and in the "format" window without it.
>[snip]
>>There are various ways to repair values if what you wanted was
>>actually numbers. But here is a simple trick:
>>o Select the appropriate cells - possibly an entire column. (You
>>can include any genuine numbers without causing any problem.)
>>o Go to Data | Text to Columns... .
>>o Leave all options as default.
>>o OK.
>
>Thank you for enlightening us to this useful information! I'm sure
>there are many other features of OpenOffice that I haven't yet "discovered".
In the interests of fairness, I ought to say that I'm pretty sure I
picked this trick up from someone on this list more knowledgeable or
experienced than me.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: Mysterious ' in Calc
Posted by Chuck Spalding <cs...@gmail.com>.
Brian,
Thank you for enlightening us to this useful information! I'm sure there
are many other features of OpenOffice that I haven't yet "discovered".
Chuck
On Sun, Jun 27, 2021 at 3:46 PM Brian Barker
<b....@btinternet.com.invalid> wrote:
> At 22:49 27/06/2021 +0100, David Deeks wrote:
> >I have discovered that I have some part-columns of figures in Calc
> >that do not sort properly, and have identified that, unlike other
> >numbers in the same columns, they all appear in the "input line"
> >with a ' preceding them - whilst appearing in the body of the
> >spreadsheet and in the "format" window without it.
>
[snip]
> No need for that. There are various ways to repair values if what you
> wanted was actually numbers. But here is a simple trick:
> o Select the appropriate cells - possibly an entire column. (You can
> include any genuine numbers without causing any problem.)
> o Go to Data | Text to Columns... .
> o Leave all options as default.
> o OK.
>
> I trust this helps.
>
> Brian Barker
>