Sure, I can help you with that! You can use the
RIGHT and
FIND functions in Excel to extract the characters after the last space in a cell. Here's how:
- Assuming your cell with the full name is in cell A1, in another cell (let's say B1), enter the following formula:
Code:
=RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))
- Press Enter to see the result. The formula will find the last space in the cell, and then return all characters to the right of it.
Let me break down the formula for you:
- The
SUBSTITUTE function replaces the last space in the cell with an asterisk (*).
- The
LEN function calculates the length of the cell content.
- The
LEN function subtracts the length of the cell content from the length of the cell content after the last space has been replaced with an asterisk. This gives us the position of the last space.
- The
FIND function finds the position of the asterisk (which is the last space).
- The
RIGHT function returns all characters to the right of the position of the asterisk.
That's it! You can now copy the formula down to other cells to extract the last name from other full names.