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

[W_Vann_Hall]

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!

Split First and Last Names

That's a little more difficult… At heart, the formula would be something like this: REPLACE({Name},1,FIND("~",SUBSTITUTE({Name}," ","~",LEN({Name}) - LEN(SUBSTITUTE({Name}," ","")))),"") That's a little hard to suss out at first glance, so let me break it down, back to front: LEN({Name}) - LEN(SUBSTITUTE({Name}," ","")) gets the difference in length between {Name} and {Name} with all spaces stripped. This gives us the index of the last space in the name, which presumably prefaces the las…

[W_Vann_Hall]

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:

[Camila_Cortes_C]

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!

[W_Vann_Hall]

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.

[Camila_Cortes_C]

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!

[W_Vann_Hall]

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.

Airtable

🇨🇱 Pat-Mat Demo - Airtable

Explore the "🇨🇱 Pat-Mat Demo" base on Airtable.

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.

[Camila_Cortes_C]

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

Thank you so much! :relaxed:

[Shel_Sun]

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

[airtableManuel]

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?

I need only the first letter of every word Big Formulas

Modifying that setup to work in your situation is definitely doable, though the biggest issue you need to consider is how many pieces you need to capitalize. Will it always be 4, or could there be more in some cases? If more, what’s the maximum number of pieces?

[Margaret_Picke1]

This has saved me a days work! Thank you