Sorting Titles that begin with "The"

Mike_Eovino · ‎Feb 28, 2016

If you’re like me, you like to catalog things. Like, perhaps, your movies. And you’re going to want to sort them by title. Which is no big deal… until you get to all of those movies that start with “The.” You don’t really want all of them in a lump in the T section, right? You’ve gotten spoiled by programs like iTunes that somehow ignore the word “The” when sorting.

Thanks to Airtable’s formula columns and string-handling capabilities, you can achieve the same effect. Let’s say you’re storing your movie titles in the Name column. All you need to do is:

Look at the first four characters of the title.
If the first four characters are "The " - chop them off, then add “, The” to the end of the title.
Otherwise, just return the title as is.

This function:
IF(LEFT(Name, 4) = "The ", RIGHT(Name, LEN(Name) - 4) & “, The”, Name)
does just that. Sort on this column, and you’re all set. Hide the column if you don’t feel like looking at it.

Be sure to look at the first four characters and look for the space after “The.” Otherwise, you’ll pick up words like “Then” or “There.” Hope this helps!

Stephen_Troell · ‎Sep 16, 2019

This is great, but I am not able to figure out how this works. Where in your formula do I enter the cell/column from which the information is being read (where the complete title currently resides)?

WilliamPorter · ‎Apr 08, 2020

@Stephen_Troell,

I see this is an old thread, but nobody ever answered your question. Let’s say you have a list of books. Values in the title field have been entered naturally, like this:

A Portrait of the Artist as a Young Man
The Sleepwalkers
Of Mice and Men
The Lord of the Rings

And say you want these records to sort as if the titles actually were

Portrait of the Artist…
Sleepwalkers
Lord of the Rings…

I deliberately leave out “Of Mice and Men” because prepositions like “of” and “in” as first word of titles are generally retained by libraries, so Of Mice and Men will sort starting with “Of”, not with “Mice”.

Anyway if you enter the titles into a field named Title, to sort as described above, you’ll want to create a formula field called something like TitleSortable, with a formula like this:

IF( LEFT( Title,3)="The", RIGHT( Title, (LEN(Title)-4)),
IF( LEFT( Title,2)="A ", RIGHT(Title,LEN(Title)-2),
Title
)

That will simply strip off the indefinite article “A” and the definite article “The” from the beginning of the title so you can sort on that field. If you’d prefer, you can move these values to the end of the title and perhaps that would be useful if you were going to export these values.

On the other hand, you could simply enter titles the way librarians have entered them since forever, that is, “Lord of the Rings, The”. Not as pretty perhaps but you don’t have to create an extra field to “fix” the problem. :slightly_smiling_face:

William
)

Stephen_Troell · ‎Apr 09, 2020

William,

Thank you so very much for answering–I sure do appreciate it! I never did get that working, so your answer is extremely helpful. Here is where I am…

I can get one line of the code to work, but having the two “IF” statements in the same formula are really giving me a challenge. I cannot seem to get them both to work. I can get one line to work in a formula column, and it will fix the titles which fit the parameters, but when combining the two, it isn’t working. I am sure I am doing something wrong. Thanks in advance!

WilliamPorter · ‎Apr 09, 2020

Stephen,

Yeah, nested IF statements are a pain. And the pain is quadrupled by the horrible, horrible formula editing keyhole (I refuse to call it a window) we’ve given in Airtable. So I will pass on a tip that someone gave me a while back when I started getting to know Airtable: Use a good text editor to write your formulas. For the Mac the choice is easy: BBEdit. In Windows we have several good choices. I sometimes use Sublime Text, but lately I’m using Brackets. The point of the editor is, it will help you balance your opening and closing brackets, parentheses, etc. And you can format your formulas on multiple lines, with tabs etc so they’re much much easier to read. Fortunately, when you copy and paste back into Airtable, it will simply ignore returns and tabs.

Here’s the formula I provided above, as I see it in the Brackets editor. I selected one of the interior parenthesese so you can see how the parens for the second IF statement are balanced.

The basic idea is really pretty simple:

If some test is true
Then the result is stated here
Otherwise, some other result

Except that when you get to step 3, it too involves a second level of if, then, else. And in theory you could keep turning that step 3 into IF statements.

Some other platforms have other structures for dealing with multiple conditions without nesting, and I wish Airtable did. FileMaker Pro for example has an alternative to IF called CASE:

CASE(
Test 1; Result 1;
Test 2; Result 2;
Test 3; Result 3;
Test 4; Result 4;
If none of the above, then default result
)

Much easier to read and debug. But once you get Brackets or another good editor, nesting the IFs does get easier. And it’s possible that Airtable has something similar and I just have not discovered it.

.

I tested the formula I gave you earlier and it worked for me, but I think I may have accidentally failed to copy the closing paren when I copied it and pasted into my first reply. Here it is, corrected:

Just copy it, replace my field name “Title” with your field name, and paste it into your editor. Remember it only takes care of removing “The” and “A”, but it sounds like that would get you a good ways to where you’re trying to go.

.

One last tip that I have been given by someone else in this forum: Airtable requires that there be no space between the function name and the opening parenthesis. So

IF( Title=“Ulysses”…

will work, but

IF ( Title=“Ulysses”…

will NOT work, because of the space between IF and the opening parenthesis. So be sure to check for that mistake. That’s the only space that seems to matter to Airtable and frankly I find it annoying, but it didn’t take me long to learn not to leave a space there.

William