MS-Excel / Functions and Formula

Getting the Last Word

To return the last word in a string of text, try this. In cell A2, type in the text Our main business focus is Excel spreadsheets. Now, click in cell B2 and type the following function:

=MID(SUBSTITUTE(A2," ","^",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))),FIND("^",SUBSTITUTE(A2," ","^",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,256)

This formula uses a combination of the MID function (to return a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify), the SUBSTITUTE function (to replace specific text in a text string), and the LEN function (to return the actual number of characters in the text string) to get the result, spreadsheets, in cell B2.

Note the use of ^. This is used to replace the necessary space character of the text string in A2. If your text includes ^, then choose another character that is not part of the text string.

[Contents] [Next]

In this tutorial:

  1. Extract Specified Words from a Text String
  2. Getting the Last Word
  3. Getting the First Word
  4. Get the Nth Word