Extracting last names | last word from a multi-word string


#1

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!


Is there a PROPER name formula in Airtable?
#2

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. :slight_smile:


[SOLVED] Find duplicates with Zapier
Limitations of text formatting formulas in LOOKUP fields
#3

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!


#4

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.


#5

Thank you very much! You’re very kind.

But :sweat_smile: 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!


#6

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 :wink: — 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.


#7

You, Sir, are the bestest to ever best. :sunglasses:

Thank you so much! :relaxed:


#8

thank you very much :call_me_hand::call_me_hand::call_me_hand::call_me_hand::call_me_hand: