How to remove the last word in a record


#1

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?


#3

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}.