sorting in calc

users
#1

Hello,
Is there a character i can enter into a cell that will come *before* numbers in the sort order?
I mean, before other cells that actually contain numbers rather than numerical strings

Thanks
Gary

#2

Gary Collins wrote

Hello,
Is there a character i can enter into a cell that will come *before*
numbers in the sort order?
I mean, before other cells that actually contain numbers rather than
numerical strings

Any character that shows up before zero in Character Map (if you are using
Windows OS).
The very first character is ! but there are 14 others (e.g. #)

Hope this helps
Pedro

#3

Gary,

You could check http://www.asciitable.com/ to see the order.

Met vriendelijke groeten, Salutations distinguées, Kind Regards,

DRIES FEYS
CORPORATE SERVICES • Specialist Software Developer

TVH GROUP NV
Brabantstraat 15 • BE-8790 WAREGEM
T +32 56 43 42 11 • F +32 56 43 44 88 • www.tvh.com
View our company movies via downloads on our website.

#4

Is there a character i can enter into a cell that will come *before* numbers in the sort order? I mean, before other cells that actually contain numbers rather than numerical strings

What about some value less than any of your real data are (or can be)? What is the range of your numerical values? In practice, this is unlikely to span the entire range that Calc can handle. If your values are all positive and non-zero, for example, then zero will do. Otherwise a sentinel value such as -999 might suffice.

If you don't want this number to appear in your list, you can hide it using Conditional Formatting with a cell style having its font colour set to white (or whatever is your cell background colour). Alternatively, you can set the cell format of your range itself to something like [WHITE][<=0]0;0 .

But the number would still be there, of course, so you'd have to take care if you did any calculations on the range such as AVERAGE().

Maybe it might work if i can convert all the relevant numbers i have entered to text but im still looking for a way to do that.

numbers and selecting text doesn't do it, ...

No: changing format of cells already containing values never changes the stored values themselves. But there are two alternative easy ways to do this:

o In another column, enter =TEXT(Xn;"0") (or whatever is a suitable format string).
o Fill down the column.
o Select the new values and cut.
o Paste them over the originals, but using Edit | Paste Special... (or Ctrl+Shift+V), selecting Numbers but not Formulae in the Paste Special dialogue.

o Select the range of values.
o Go to Data | Text to Columns... .
o In the Text to Columns dialogue, under Fields, click on the column header (probably currently Standard).
o In the "Column type" drop-down, select Text.
o OK.
Voilà!

Ive managed to solve my problem in a roundabout way. To give the context, i have a column containing strings of length 1 to 3 which may contain nonstandard characters and which i must sort in a specific order (not the usual alphabetic order). To achieve this i have a table assigning a number (col B) to each char (col A) in sheet 2, defined as a range 'xlit'.

So this table must indeed have a small, finite range of values (presumably integers) in column B. Surely your lookup formula just needs to ascribe some numeric value outside and below this range instead of the blank character or empty cell that you may have at present?

I trust this helps.

Brian Barker