On Mon, 28 Jun 2021 11:30:28 +0100 David Deeks <prof.david.de...@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.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 > > > -- Rory O'Farrell <ofarr...@iol.ie> --------------------------------------------------------------------- To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org For additional commands, e-mail: users-h...@openoffice.apache.org
