how do I sort a numbers 08 spreadsheet using letters and digits without getting a100 between a10 and a11?

How about separate columns, one for 'type', one for 'number', and one for 'name', etc.?

Then you can sort by 'type' and within that by 'number' or 'name'. Or by 'name' and within that by 'type', etc.

SG

Hi 'bert,

Separating the category and number within that category as suggested above by SG will work. But if you want to keep the category and number as a single value in a single column, that can be easily done.

As soon as you append that a or sf to the number, you make the 'number' a text string, and Numbers sorts it using text rules.

If you want these to sort 'correctly' you must use enough leading zeroes to make all of the 'numbers' have the same number of digits. If you think you'll eventually have 1000 or more blueray disks in a single category, then you'll need the number part of that category (or of all categories) to include at least four digits.

As text, the digits 0 - 9 are treated as a downward extension of the alphabet. Sorting using text rules considers the strings from left to right, one character at a time. The first character that is different in two strings determines their sorting order.

a001, a002…a099, a100, a101…a999 will sort as expected, but the next item in that category, a1000 will sort between a100 and a101.

a0001, a0002…a0099, a0100, a0101…a0999, a1000…a9999 will sort as expected, but the next item in that category, a10000 will sort between a1000 and a1001.

Leave the cell format set to automatic. If you set it to numbers, then make an entry that Numbers is unable to interpret as a number, the entry will override the format setting anyway.

Regards,

Barry

Hi applebertt,

I agree with SG that separate columns will allow more flexible sorting.

In case you are wondering how to split your codes into separate columns, the LEFT and RIGHT functions will do it.

If you use 3 digits, the codes should sort correctly. Here is an automatic way to convert 2 digits to 3 digits.

Original types are in Column A

Fill these formulas down:

B2=LEFT(A2,LEN(A2)-2)

C2=RIGHT(A2,2)

D2="0"&C2

This won't work on any codes where you have already entered greater than 99 as the number.

If you want to combine the type and number, add another column (E)

E2=B2&D2

Will show a001

Constructed in Numbers '09

Regards,

Ian.

Edit: I see Barry has already given you the tip on 3 digits. Ian.

Hi 'bert,

Pretty standard rules: Numbers are sorted by their numeric values, strings are sorted using alphabetical sorting. The value in a cell can be a number or it can be a string, but it can't be both.

Here's a couple of examples of a formula that may speed your task of inserting leading zeros to the number portions of your strings. As written, the method will require an added column for each category. The process is cumulative, so you should end up with all of the letter and number combinations listed in the final column.

Select that column, Copy, then with the column still selected, go Edit (menu) Paste Values. (This may be called 'Paste formula results' in the current version of Numbers.)

Once that's done, you can delete the intervening calculation columns (and the original column containing the unconverted 'numbers').

In the example, the original 'numbers' are in column D, Column E contains the formula that converts the 'a' values (and copies of the original values in the sf category), and column F contains copies of the converted 'a' values and the newly converted 'sf' values.

E2: =IF(LEFT(D,1)="a",LEFT(D,1)&RIGHT("0000"&RIGHT(D,LEN(D)-1),4),D)

F2" =IF(LEFT(E,2)="sf",LEFT(E,2)&RIGHT("0000"&RIGHT(E,LEN(E)-2),4),E)

The bold parts of the second formula show the edits needed for each successive column (new letter category code inside the quotes, and the length in characters of the new letter category code in the three locations shown. The formula may be entered in cell E2, then filled right for as many columns as there are categories. After editing as described, the formulas may be filled down to the last row containing entries.

Here is an improved version of the same formula. This version gets the letter category code from the top cell in each of the columns, and calculates the length of that code in the three places shown in bold above, IOW, it does the editing above automatically,using the category codes you enter in row 1 of each column, as you fill it across and down.

E2: =IF(LEFT(D,LEN(E$1))=E$1,LEFT(D,LEN(E$1))&RIGHT("0000"&RIGHT(D,LEN(D)-LEN(E$1)) ,4),D)

Enter the codes ( a, sf, etc) in row 1 of each new column column to be used in the conversion.

Enter the formula in E2 (row 2 of the new column to the right of the column containing your original numbering).

Fill the formula right to the last new column to be used in the conversion.

Fill all formulas down tote end of the table.

Images:

Unsorted table (first 18 rows). Note that 'a' values have been converted in column E, while 'sf' values have not.

Sorted on values in column F. The gap between a0012 and a0029 is where I had inserted a set of sf values.

Further down the sorted table, showing the cross from a0099 to a0100, and the first five rows of sf items:

Note: Column B contains a set of generated random numbers used to 'unsort' the table after generating the 'original numbers' in column D.

Regards,

Barry

applebertt wrote:

I have columns for title, starring, year released, movie type, ref# and cover. I use the column ref# to sort my movies.

Glad to see you have found a solution. My original advice still stands: separate the 'type' and 'number' into separate columns and keep them separate. Then you can easily produce a ref# like this ("padding" the number with leading zeros) and sort as you always have.

The screenshot is from Numbers 3 but the approach works equally well in previous versions. The formula in C2 here, copied down the column, is:

=A2&RIGHT("00000"&B2,3)

Suppose in the future you end up with over 1000 items of one type, and again need to change the ref# to sort properly. No problem. Just change the formula in C2 to =A2&RIGHT("00000"&B2,4). Then copy it down the column. You're done in less than a minute.

As you can see, it's far easier to construct a sort column from other columns (it takes just one relatively simple formula) than it is to break an existing composite ref# into its components to "fix" it some time in the future when you've outgrown your numbering scheme... unless you enjoy working with a bunch of complicated formulas and extra columns when you need to make adjustments.🙂

In general with spreadsheets it pays to keep only one type of information in a column. When needed, construct a composite "on the fly" with a simple formula.

SG

SGIII writes:

[I]t's far easier to construct a sort column from other columns (it takes just one relatively simple formula) than it is to break an existing composite ref# into its components to "fix" it some time in the future when you've outgrown your numbering scheme.

In general with spreadsheets it pays to keep only one type of information in a column.

Good points! Just discovered I could stick a star on your post 🙂

Regards,

Barry