Sort Ascending (Ignoring "The" and "A")

Hi Darren,

I find it easier to place the column to the right of the column containing the data, but it really doesn't matter where it is, as long as it is part of the same table as the data. A convenient place to put it might be the rightmost column of the table, which would keep it out of the way of data entry, particularly if you also hid the Sort column.

The key to making it work is to make sure the formula references data in the correct column and on the same row as the current iteration of the formula.

Badunit's formula is for a table in which the full titles are listed in column B. Mine are for titles listed in column A.

Badunit used only the column letter to indicate the cell containing the title; I used the column letter and row number of the cell. Numbers is usually smart enough to know whether "B" refers to the whole of column B or to the cell in column B on the same row as the formula.

Other that that, the formulas are essentially the same;

**: =IF(LEFT(B,2)="a ",RIGHT(B,LEN(B)-2), IF(LEFT(B,4)="the ",RIGHT(B,LEN(B)-4),B))

Barry: =IF(LEFT(A2,2)="a ",RIGHT(A2,LEN(A2)−2),IF(LEFT(A2,4)="the ",RIGHT(A2,LEN(A2)−4),A2))

In either example, the formula is entered in row 2 of its column (cell B2 in my example), then filled down to the last row of the table containing the titles.

If the column is visible, you can sort the list using Sort Ascending in the Sort column's contextual menu; if the column i hidden, select the table, then choose Sort and Filter options from the Table menu or click the Sort and Filter button beside the Format brush.

Regards,

Barry

The only way I can think of is to create another column with a formula like this to strip off the "A " or "The " then sort on this column instead:

=IF(LEFT(B,2)="a ",RIGHT(B,LEN(B)-2), IF(LEFT(B,4)="the ",RIGHT(B,LEN(B)-4),B))

You can set the text color to white so the text doesn't show, make the column less wide, and put some text such as "sort on this column" in the header.

Reuben Feffer wrote:

Thanks for the reply.

I guess I should've added, "is there an easy way" to do what I want to do? These formulas and maths are beyond me.

With that caveat, the answer is no, there is not.

Spreadsheets are more than just a place to store data, they are usually about "formulas and maths". Might be time to crack open the users manual and the formulas and functions guide. I provided the basic formula, you should be able to modify it for your needs. Otherwise you will not be able to do what you want.

I suspect iTunes does not have the intelligence to NOT sort by that method if you wanted to keep the A's and The's sorted alphabetically (or maybe it does, but I would never use it that way). iTunes is geared for sorting titles in the way most users would sort them so it was designed to strip off the A's and The's. Numbers is geared toward the sorting of random information with no rules other than to make it alphabetical.

Sorry to resurrect this old post, but I wanted to know this: Does the blank column that will hold this formula need to be to the right or left of the column it will be "stripping"? I have pasted it on the left and right side of the column needing stripping and it does not return a result or error. I must be missing something elemental. 🙂

Oh, wait. I just realized the examples are for two-column tables. Mine has about 9.

I like the idea of putting the formula column at far right, but how do I "point" it at the column of text i want it to "strip"?

Thanks.

Perhaps a feature request to send to the Numbers team. You can find it under the Numbers menu > Provide Numbers Feedback. That takes you to the same page as this link.

Thank you, sir! I will give it a try.

Never mind. I got it! Thanks a million. 🙂