Returning the Final Word

Nathan has a cell (A1) that contains a sentence. He needs a formula that will return the last word in the sentence. He thought he had the formula, but it also considers the punctuation after the last word as part of the word. Nathan wonders if there is a way, in a formula, to return just the last word.

It is possible to use the SUBSTITUTE function to strip away any trailing punctuation, in this manner:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTAFTER(A1," ",COUNTA(TEXTSPLIT(A1," "))-1),".",""),"?",""),"!","")

The formula will strip a single period, question mark, or exclamation mark from the end of the text. It won't work if the text in A1 consists of a single word, as it determines word the words in the sentence by the spaces separating the words. It also won't work satisfactorily if there may be multiple punctuation marks at the end of the sentence.

Because the formula uses the TEXTAFTER and TEXTSPLIT functions, it will only work in Excel 2024 and Excel 365. Speaking of which, if you are using Excel 365, then you can call upon the REGEX functions to help. Here, for example, is a formula that may work:

=REGEXEXTRACT(A1, "(\w+)\W*$", 2)

This pattern returns any word, defined by (\w+), where a word consists of consecutive characters consisting of letters, numbers, or underscores. This word can be followed by any number of non-word characters, \W*, at the end of the text, $.

This pattern can lead to some undesired results if your final word contains a dash (as in "first-take"), a contraction (as in "can't" or "don't"), or if you want numbers and underscores eliminated from consideration. In that case, the following will provide a better result:

=TAKE(REGEXEXTRACT(A1,"[a-z]+(-[a-z]+)?('[a-z]+)?",1,1),,-1)

The pattern is more complex, but it basically uses REGEXTRACT to construct an array of each word in A1 that consists of letters and, optionally, a dash or apostrophe. The TAKE function then returns the final element in the array.

If you don't want some of the drawbacks inherent in these approaches or if you want something that will work reliably in older versions of Excel, then your best bet will be to create a user-defined function in VBA:

Function LastWord(cell As Range) As String
    Dim txt As String
    Dim i As Long
    Dim ch As String
    Dim word As String
    Dim started As Boolean

    txt = cell.Value
    word = ""
    started = False

    For i = Len(txt) To 1 Step -1
        ch = Mid(txt, i, 1)
        If ch Like "[A-Za-z-']" Then
            word = ch & word
            started = True
        ElseIf started Then
            Exit For
        End If
    Next i

    LastWord = word
End Function

The function steps backward through the contents of the desired cell and constructs a word that consists of only letters, dashes, and apostrophes. When it reaches what it considers as the beginning of the word, then it returns the constructed word. You use the function in your worksheet in this manner:

=LastWord(A1)

Of course, Nathan said that he wanted a formula to get his desired result, but that may not be possible in all instances depending on the version of Excel he is using.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11792) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.