How do I fix sorting issues when Numbers don't sort correctly?

Once you upgrade Numbers the formula can be as simple as this:

=SORTBY(A,TEXTAFTER(A," "))

I only needed a single formula in cell B2 in the example. No need to fill it down the column. Numbers does the work "spilling" the values into the cells.

SG

If you want to sort the numbers as numbers and not text values then you can try something like this:

Add a new column, in the example column B.

In B2 of the example filled down:

=TEXTBEFORE(A2," ")*1

This grabs the leading "number" and coerces from text to a number by multiplying it by 1.

Then sort on column B.

Note: if your region uses , as the decimal separate then use ; instead of , in the formula, e.g.,

=TEXTBEFORE(A2;" ")*1

More on the function here:

TEXTBEFORE - Apple Support

SG

To your column A, add a column B

In B2 use this formula TEXTSPLIT(A2;” “)

It will ask you to add more columns in relation to the length of the A2 string

Drag B2 down and you will get like in TABLE 1

Now order column C to get as in column A of TABLE 2

You can choose to hide the columns you don't need as in TABLE 3

Another possibility is that you add a column to TABLE 3 com in TABLE 4 and use this formula in A2

SORTBY(B;D;1)

In this way if you add data to column B you automatically sort column A and always keep hidden columns from C onwards

If you don't want to see the 0 in the empty lines of column A choose conditional highlighting

Equal to number 0

Custom style

And choose white as the font color

Hi Puzzle999,

If your table looks something like this:

then Column A is formatted as Text (left aligned by default) and sorting by Column A will sort the table alphabetically, not numerically.

Here we go, after sorting Table 1 by column A:

Select Column A and Format Panel (on the right of your document window) and Cell tab > Data Format > Automatic (or Number). That will convert Column A to numbers (right aligned by default).

Now sort:

Please call back with questions.

Regards,

Ian.

It is sorting the column correctly. Digits sort before alpha characters.; 0-9 come before "A" and they sort "alphanumerically" as characters, not as numbers (100 is not one hundred, it is the characters "1" "0" "0").

It sounds like you want to ignore the leading numbers and sort by the characters after the numbers. Note that rows like "5 COBS" and "20 COBS" will be ranked the same if you remove the digits, both being "COBS", so whichever comes first in the column will sort higher/first.

I will assume all of your strings have a leading number and a space character.

If your strings are in column A (starting in A2), you can remove the leading digits and the space character with the following formula in cell B2:

=TEXTAFTER(A2," ")

fill down to complete the column

Then sort by column B

If the number matters and you want to ensuire rows like "5 COBS" and "20 COBS" sort numerically, you'll need a column C

C2 = TEXTBEFORE(A2," ")*1

Fill down

Use the Organize sidebar to create a sort that uses both column B and C

I'm not suggesting you do what I say next because the formulas are more complicated but it is another possibility.

If you are using Numbers version 14.4 , an alternate idea is do the sort in another column using formulas without actually sorting the table.

In column B, use the following formula:

=SORTBY(A,REGEX.EXTRACT(A,"(?<=\d ).+"))

or, to also sort numerically so "COB 5" is sure to come before "COB 20",

=SORTBY(A,REGEX.EXTRACT(A,"(?<=\d ).+"),1,1×REGEX.EXTRACT(A,"\d+"))

No manual sorting is required. The formula does it all.

Puzzle999 wrote:

The version I'm using is 12.7

So much easier using reasonably up-to-date versions of Numbers, but here is one way you can accomplish the sort with an old version.

Set up a sort column with this formula:

=RIGHT(A2,LEN(A2)−FIND(" ",A2))

Then click the column letter of the new sort column and choose Sort Ascending.

Result:

SG

> This grabs the leading "number" and coerces from text to a number by multiplying it by 1.

You can also use the VALUE() function to coerce text to a numeric result:

=VALUE(TEXTBEFORE(A2,"")

Camelot wrote:

> This grabs the leading "number" and coerces from text to a number by multiplying it by 1.

You can also use the VALUE() function to coerce text to a numeric result:

=VALUE(TEXTBEFORE(A2,"")

Nice.

For those adopting this clearer approach be sure to add the missing ) at the end of the formula, .e.g.,

=VALUE(TEXTBEFORE(A2," "))

SG

Puzzle999 wrote:

The version I'm using is 12.7 perhaps that's the issue. This version of [Numbers] doesn't recognise SORTBY or TEXTAFTER.

I thought TEXTAFTER and TEXTBEFORE were available in version 11, if not earlier. SORTBY arrived in version 14.4

Hi Puzzle999,

We are not looking over your shoulder. A screen shot will help us to see what you see.

Regards,

Ian.