Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

How to remove the last word in a record

Topic Labels: Formulas
2336 1
cancel
Showing results for 
Search instead for 
Did you mean: 

Hello everyone,

How do you remove the last word from within a string in a record so that another record shows the same without the last word?

1 Reply 1

This just came to me tonight. Assuming your target string is in a field called {String}, the formula would be

IF(
    FIND(
        ' ',
        TRIM(
            {String}
            )
        ),
    LEFT(
        SUBSTITUTE(
            TRIM(
                {String}
                ),
            ' ',
            '|',
            LEN(
                TRIM(
                    {String}
                    )
                )-LEN(
                    SUBSTITUTE(
                        TRIM(
                            {String}
                            ),
                        ' ',
                        ''
                        )
                    )
            ),
        FIND(
            '|',
            SUBSTITUTE(
                TRIM(
                    {String}
                    ),
                ' ',
                '|',
                LEN(
                    TRIM(
                        {String}
                        )
                    )-LEN(
                        SUBSTITUTE(
                            TRIM(
                                {String}
                                ),
                            ' ',
                            ''
                            )
                        )
                )
            )-1
        )
    )

That’s a mess, I know, but what it does is it replaces the last space character in {String} with a non-alphanumeric character (in this case ‘|’, the vertical bar) and then uses a LEFT() function to copy the string up to, but not including, the vertical bar.

To figure out which space character is the last in the string — or, to put it another way, to figure out how many space characters are in the string — the formula subtracts the length of the string with all space characters removed from the length of the string.

The repeated calls to TRIM() eliminate any pesky leading or trailing spaces that may have slipped in, and the surrounding IF() statement makes sure it doesn’t return an error if you feed it a one-word {String}.