Is there a PROPER name formula in Airtable?

The answer, as always, is “it depends.”

If this was a {firstName}{lastName} universe, it would be trivial to create a formula to fix proper names. But even if you try to force the issue by prompting for such input, some joker like me is going to come along and shove ‘W Vann’ into the {firstName} field, and all bets are off. That said, it should be possible to come up with a formula that almost always gets things right.

Well… almost always gets things rightish. Case in point: Google ‘di castro’. Skimming down the first page of responses, I find Anna Di Castro, Jane di Castro, Angelica DiCastro (who later gives her name as Angelica Di Castro), Joseph DiCastro, and Simon Dicastro. Short of a Zap that integrates with God, you are going to get somebody’s name wrong…

If your data is arriving as {firstName}{lastName}, life is much simpler — so I will assume you are starting with {Name}, because that’s life.

My [other] assumptions: Up to five name components per name, separated by spaces or a hyphen, including prefixes and suffixes; no more than one hyphenated name segment; a minimum of two name components (sorry, Sting and Cher!).

And the results?

The good news: It can be done, with no need for middleware or manual intervention.
The bad news: It’s a bit of a mess.

Here is a base demonstrating my take on a proper name function. (Actually, this is a third iteration of my earlier name processing demo base with a newly added table containing the proper name routines. The two sets of processes are not integrated.)

As implemented, this algorithm requires 10 (!) — 9 typically hidden — fields to perform a reasonably rightish proper name formatting. Conceivably, it could be implemented more compactly with the many FIND() functions incorporated inline rather than by reference; I chose to break the process into chunks in hopes of making it somewhat intelligible.

My handling of hyphenated last names is, well, a little disappointing. Rather than having to search for a space OR a hyphen three times or perform two separate passes, I cheat: If a hyphen exists, I note its location in the string, replace it with a space, capitalize the first character and any character that follows a space, and return the hyphen to the string.

Since much of the processing is triggered by the presence of space characters, leading and trailing spaces are trimmed, and redundant internal spacing normalized. Finally, in support of dynasties, the suffixes ‘II’, ‘III’, and ‘IV’ are explicitly corrected. (If your students are particularly proud of their credentials, you may need to add support for ‘MD’, ‘PhD’, and the like.)

Needless to say, the routines are very English-language, Western (U.S.) society biased, and your mileage may vary.

Still, you should be able to use these routines confident the resulting formatted names will usually be more-or-less rightish — which is about the best one can ask when it comes to names. Inevitably, just when you think you have provided for any eventuality, your next sign-ups will be from bell hooks, e. e. cummings, The Artist Formerly Known As Prince, and Kiefer William Frederick Dempsey George Rufus Sutherland.

The answer, as always, is “it depends.”

If this was a {firstName}{lastName} universe, it would be trivial to create a formula to fix proper names. But even if you try to force the issue by prompting for such input, some joker like me is going to come along and shove ‘W Vann’ into the {firstName} field, and all bets are off. That said, it should be possible to come up with a formula that almost always gets things right.

Well… almost always gets things rightish. Case in point: Google ‘di castro’. Skimming down the first page of responses, I find Anna Di Castro, Jane di Castro, Angelica DiCastro (who later gives her name as Angelica Di Castro), Joseph DiCastro, and Simon Dicastro. Short of a Zap that integrates with God, you are going to get somebody’s name wrong…

If your data is arriving as {firstName}{lastName}, life is much simpler — so I will assume you are starting with {Name}, because that’s life.

My [other] assumptions: Up to five name components per name, separated by spaces or a hyphen, including prefixes and suffixes; no more than one hyphenated name segment; a minimum of two name components (sorry, Sting and Cher!).

And the results?

The good news: It can be done, with no need for middleware or manual intervention.
The bad news: It’s a bit of a mess.

Here is a base demonstrating my take on a proper name function. (Actually, this is a third iteration of my earlier name processing demo base with a newly added table containing the proper name routines. The two sets of processes are not integrated.)

As implemented, this algorithm requires 10 (!) — 9 typically hidden — fields to perform a reasonably rightish proper name formatting. Conceivably, it could be implemented more compactly with the many FIND() functions incorporated inline rather than by reference; I chose to break the process into chunks in hopes of making it somewhat intelligible.

My handling of hyphenated last names is, well, a little disappointing. Rather than having to search for a space OR a hyphen three times or perform two separate passes, I cheat: If a hyphen exists, I note its location in the string, replace it with a space, capitalize the first character and any character that follows a space, and return the hyphen to the string.

Since much of the processing is triggered by the presence of space characters, leading and trailing spaces are trimmed, and redundant internal spacing normalized. Finally, in support of dynasties, the suffixes ‘II’, ‘III’, and ‘IV’ are explicitly corrected. (If your students are particularly proud of their credentials, you may need to add support for ‘MD’, ‘PhD’, and the like.)

Needless to say, the routines are very English-language, Western (U.S.) society biased, and your mileage may vary.

Still, you should be able to use these routines confident the resulting formatted names will usually be more-or-less rightish — which is about the best one can ask when it comes to names. Inevitably, just when you think you have provided for any eventuality, your next sign-ups will be from bell hooks, e. e. cummings, The Artist Formerly Known As Prince, and Kiefer William Frederick Dempsey George Rufus Sutherland.

The answer, as always, is “it depends.”

If this was a {firstName}{lastName} universe, it would be trivial to create a formula to fix proper names. But even if you try to force the issue by prompting for such input, some joker like me is going to come along and shove ‘W Vann’ into the {firstName} field, and all bets are off. That said, it should be possible to come up with a formula that almost always gets things right.

Well… almost always gets things rightish. Case in point: Google ‘di castro’. Skimming down the first page of responses, I find Anna Di Castro, Jane di Castro, Angelica DiCastro (who later gives her name as Angelica Di Castro), Joseph DiCastro, and Simon Dicastro. Short of a Zap that integrates with God, you are going to get somebody’s name wrong…

If your data is arriving as {firstName}{lastName}, life is much simpler — so I will assume you are starting with {Name}, because that’s life.

My [other] assumptions: Up to five name components per name, separated by spaces or a hyphen, including prefixes and suffixes; no more than one hyphenated name segment; a minimum of two name components (sorry, Sting and Cher!).

And the results?

The good news: It can be done, with no need for middleware or manual intervention.
The bad news: It’s a bit of a mess.

Here is a base demonstrating my take on a proper name function. (Actually, this is a third iteration of my earlier name processing demo base with a newly added table containing the proper name routines. The two sets of processes are not integrated.)

As implemented, this algorithm requires 10 (!) — 9 typically hidden — fields to perform a reasonably rightish proper name formatting. Conceivably, it could be implemented more compactly with the many FIND() functions incorporated inline rather than by reference; I chose to break the process into chunks in hopes of making it somewhat intelligible.

My handling of hyphenated last names is, well, a little disappointing. Rather than having to search for a space OR a hyphen three times or perform two separate passes, I cheat: If a hyphen exists, I note its location in the string, replace it with a space, capitalize the first character and any character that follows a space, and return the hyphen to the string.

Since much of the processing is triggered by the presence of space characters, leading and trailing spaces are trimmed, and redundant internal spacing normalized. Finally, in support of dynasties, the suffixes ‘II’, ‘III’, and ‘IV’ are explicitly corrected. (If your students are particularly proud of their credentials, you may need to add support for ‘MD’, ‘PhD’, and the like.)

Needless to say, the routines are very English-language, Western (U.S.) society biased, and your mileage may vary.

Still, you should be able to use these routines confident the resulting formatted names will usually be more-or-less rightish — which is about the best one can ask when it comes to names. Inevitably, just when you think you have provided for any eventuality, your next sign-ups will be from bell hooks, e. e. cummings, The Artist Formerly Known As Prince, and Kiefer William Frederick Dempsey George Rufus Sutherland.