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 <ofarr...@iol.ie> wrote: > > On Mon, 28 Jun 2021 11:30:28 +0100 > David Deeks <prof.david.de...@gmail.com <mailto: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 <mailto:ofarr...@iol.ie>>
