How can I force sort a numeric table

Numbers sees 5/1 and 5/3 and (correctly) sorts the 5/1 to come first. Because of your stock number format you may need to set up a sort index column. What is your overall numbering system?

SG

Hi Vedeco,

One way is to split your numbering system into a hierarchy such as this:

That table uses the LEN, FIND, VALUE, LEFT, MID and RIGHT functions to split the Stock Item (Column A) into components. You can hide the intermediate columns (B, C, D) and sort or filter by Columns E, F, G.

Note that Numbers has converted 4/1/02 to a Date & Time format (aligned right). No problem.

Please reply if you want the formulas. It is late and I must go to bed 😉.

Regards,

Ian.

Hi Vedeco,

Numbers interprets that set of three numbers (and two separators) as a text string (or in cases where such an interpretation makes sense, as the date part of a Date & Time value).

Text strings, whether composed of letters, numerals, or a combination of the two are sorted alphabetically, starting from the left, and working one character at a time to resolve 'ties'.

1, 10, 100, 1000, and 1000000 will all sort before 2.0 if the values are text, rather than numbers.

To force a "numerical" sort you need to either separate the parts of the 'number' into separate columns (as in Ian's example), then set up a sort rule that sorts by column E, then by column F, then by column G, OR you need to reformat the 'numbers' so that have the same number of digits in the first part, the same number of digits in the second part, and the same number of digits in the third part.

Using the first and last values in Ian's example:

The last value would remain unchanged.

The first value would become 05/132/05

These would require setting the cell format for the column containing the numbers as Text to maintain the leading zero.

Regards,

Barry

What is your overall numbering system?

If we know more about your numbering system it should be easy to come up with a simple script or formula to give you a sort index column that will let you sort the way you want.

SG

Vedeco wrote:

Thanks for your help. Someone else came up with the simple suggestion of splitting the numbering into three columns and then sorting in order. Sounds the simplest solution to me. Thanks for your input though.

I actually preferring just having one sort index column in my table rather than a bunch of extra columns. I find that neater and tidier. It involves the first step of splitting the string, but then puts it back together again in a sortable form in one column. It's not hard to do, given a little understanding of your overall numbering system, which is why I asked.🙂

But if you just want to do the first step (split the numbers into separate columns) then a script is often easier to use than setting up formulas. No scripting knowledge needed, just copy/paste and click. In less than 30 seconds or so and you'll have your result.

Here's my result:

1. Copy-paste script below into Script Editor
2. Select the cells containing the numbers you want to split into columns (don't include blank cells)
3. Click the triangle 'run' button in Script Editor.
4. Click once in a destination cell (in my example B2)
5. Command-v to paste.

If you're interested in how to have just one sortable column, just post a little bit about that numbering system.

SG

tell application "Numbers"

tell front document's active sheet

set pasteStr to ""

tell (first table whose selection range's class is range)

repeat with c in (get selection range)'s cells

set v to c's value

set pasteStr to pasteStr & ¬

v's word 1 & tab & v's word 2 & tab & v's word 3 & return

end repeat

end tell

end tell

end tell

set the clipboard topasteStr

return pasteStr