Jul 16, 2017 12:04 PM
The question of how to extract the last name from a combined “first [middle][…] last [suffix]” name string – or the last word from a multi-word string – comes up from time to time. I offer a solution (with caveats) in a reply to a recent support post. My example is name-oriented but could easily be adapted to an arbitrary string. Hope this will save someone some time!
Jul 19, 2017 04:04 AM
I’ve created a second version of this base that addresses a few edge cases. Note that these require manual ‘hinting’ be performed when the name is entered; they cannot be automated (well, at least not without an absurd amount of work).
Support for compound given names or surnames with “^” standing in for the embedded space (Mary^Lou Retton, Gabriel Garcia^Marquez).
Support for familiar name (e.g., nickname, diminutive, alternative forename) with “#[FamiliarName]” appended to {WholeName} (C. S. Lewis#Jack, F. Scott Fitzgerald#Scott).
As the son of a ‘Mary Lou’ who goes by his middle name, you can see why this is of particular interest to me. :slightly_smiling_face:
Dec 15, 2018 04:48 PM
Hi, I have another name configuration as in Chile we use the following syntax for a name:
Camila Francisca Cortés Contreras
[First Name] [Second Name] [Father’s 1st Lastname] [Mother’s 1st Lastname]
How could I go splitting that into
Cortés Contreras - Camila Francisca?
Thanks!
Dec 16, 2018 01:31 PM
Well, the simplest solution — where you are assured all names in your base have first, last, patronymic, and matronymic – would be to use this formula:
RIGHT(
Name,
LEN(
Name
)-FIND(
' ',
Name,
FIND(
' ',
Name
)+1
)
)&
' - '&
LEFT(
Name,
FIND(
' ',
Name,
FIND(
' ',
Name
)+1
)
)
If you don’t live in the best of all possible worlds, you’ll probably want to add checks for names missing the matronymic (wait: actually, the formula handles that OK) or containing compound first or second names.
Dec 16, 2018 02:34 PM
Thank you very much! You’re very kind.
But :grinning_face_with_sweat: what I actually meant was to have the name field separated in two fields:
From one field containing Camila Francisca Cortés Contreras
To one with
Cortés Contreras
And the other with
Camila Francisca
Also I have a few persons who are listed without second names, and even some without matronymic as you imagined.
How could I go getting two fields?
(And also, is someplace to learn this formula code for dummies like me?)
Thank you so much!
Dec 17, 2018 02:54 AM
If you look closely at the formula, you’ll see it’s essentially two separate functions, a RIGHT()
and a LEFT()
, joined with ‘&' - '&
’. To break the name into two fields, the field holding the patronymic and matronymic should contain the RIGHT()
function up through the closing parenthesis preceding the first ampersand (’&
’), and the first- and second-name field should contain the LEFT()
function through the end of the formula. That is, the pat/mat formula should be
RIGHT(
Name,
LEN(
Name
)-FIND(
' ',
Name,
FIND(
' ',
Name
)+1
)
)
and the first/second name field
LEFT(
Name,
FIND(
' ',
Name,
FIND(
' ',
Name
)+1
)
)
As far as handling of exceptions goes, I can’t think of any non-AI method of processing correctly both first-second-patronymic
names and first-patronymic-matronymic
names that doesn’t involve some sort of manual hinting. You could use something similar to the hinting I use in my demo name base to indicate preferred or nicknames, flagging each matronymic with a non-character (e.g., Camila Francisca Cortés #Contreras
), but it would probably be more efficient and maintainable to insert some sort of placeholder for any missing name segment.
For example, in the case of a missed matronymic, you might have
‘Camila Francisca Cortés .
’ (note the trailing period). Likewise, with a missing second name, you would have ‘Camila . Cortés Contreras
’. I’d then wrap each of your formulas with SUBSTITUTE([Function],' .',' ')
. This replaces the two-character, space
/period
(’.
’) sequence with the empty string (that is, it deletes it) to remove the period hints:
SUBSTITUTE(
RIGHT(
Name,
LEN(
Name
)-FIND(
' ',
Name,
FIND(
' ',
Name
)+1
)
),
' .',
''
)
SUBSTITUTE(
LEFT(
Name,
FIND(
' ',
Name,
FIND(
' ',
Name
)+1
)
),
' .',
''
)
There’s a quick demo base (actually, the scratch base I used to debug these routines) here demonstrating this functionality.
As far as learning the code, I’d say start with the tutorials and introductions in the Help Center and then make ‘Using Airtable’ your friend. Every now and then — probably whenever my meds need adjusting :winking_face: — I return to my preliminary notes for an Airtable book… but unless a rich uncle no one in the family remembers were to die and leave me a stipend, I’ll never manage to free the time actually to write it. Rest assured, it’s easier than you may think.
Dec 17, 2018 05:14 AM
You, Sir, are the bestest to ever best. :smiling_face_with_sunglasses:
Thank you so much! :relaxed:
Dec 18, 2018 12:37 PM
thank you very much :call_me_hand: :call_me_hand: :call_me_hand: :call_me_hand: :call_me_hand:
Nov 30, 2019 12:50 AM
Awesome, how can i write automatically all Words at the beginning Big example:
hallo hello seas herbert sabrina germany
into
Hallo Hello Seas Herbert Sabrina Germany?
Nov 15, 2021 01:08 AM
This has saved me a days work! Thank you