Columns Not Sorting - Numbers 3.5.3

Hi Rueben,

Numbers reworked how it sorts to be more in line with how it is dine with other programs. Apparently this has advantages as well as dis. You have been caught by the latter.

You need a sort save formula in your emissions column. In order to help we need to know where the data in the column comes from and how you are calculating it.

quinn

A number can't include characters that aren't numbers (except decimal and thousands separators). Since your entries include "g/km" they are text, not numbers. Text sorts one character at a time; 10 g/km comes before 7 g/km because 1 is less than 7.

If you want them to sort numerically (where 7 is less than 10), put just the number into the column. (Maybe include the unit in the header: "Emissions (g/km)" or put it in a separate column, with no border to separate it from the value column.)

If the values in your column E are indeed text, you can change them to numbers by hand, or you can extract just the number portion like this:

The formula in B2 of my Table 1, copied down the column, is:

=REPLACE(A2,FIND(" ",A2,1),5,"")×1

This simply replaces the ' g/km' (the last five characters when the space is included) with nothing. Multiplying the result by 1 is one way to be sure Numbers knows you want a number.

Then you can copy and Edit > Paste Formula Results those numbers wherever you want them and apply a custom format (Create Custom Format at the bottom of the Data Format dropdown) so that they display as shown in your screenshot and in column A of my 'Sorted' Table:

Then the column will sort as you expect, because it contains "true" numbers. The 'g/km' is now just part of the format.

SG