How can I pull only last names from a list containing two and three word names

Hello Oren Lifshitz,

Thanks for reaching out with your Excel question about extracting last names.

You can use the following formula to pull out the last word from each name, which typically corresponds to the last name:

Let's assume your list of names is in Column A, starting from cell A1.

You can put this formula in cell B1 (or any other empty cell next to your list):

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))

How to Use It:

1. Copy the formula above.
2. Paste it into cell B1 in your Excel sheet.
3. Press Enter. You should see the last name from cell A1 appear in B1.
4. Drag the fill handle (the small square at the bottom-right corner of cell B1) down to apply the formula to the rest of your names in Column A.

In simple terms, this formula works by:

• Finding the very last space in the name.
• Grabbing everything after that last space (which is your last name).
• And then tidying it up by removing any extra spaces.

It's designed to work whether a name has two words (e.g., "John Doe") or three words (e.g., "Jane Alice Smith").

Example:

If your names are in Column A:

I hope this helps you pull out those last names! Please let me know if you have any trouble or if anything is unclear.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment”.  

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.