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.m.bar...@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-unsubscr...@openoffice.apache.org > For additional commands, e-mail: users-h...@openoffice.apache.org >
