Return cell characters after space
 

I have a cell with 'Mr J. Smith' and want to return in another cell 'Smith'.
As there are many other examples, I need a function that will seek out the
last ' ' in the cell content and then return all characters to the right
hand-side of this ' '.

e.g. 'Mr K Peters' would return 'Peters'

Please let me know if I can clarify!

Answer: Return cell characters after space
 

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:

1. 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," ","")))))

   ---

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

Return cell characters after space
 

Hi Andy

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

--
Jacob (MVP - Excel)


"Andy" wrote:

I have a cell with 'Mr J. Smith' and want to return in another cell 'Smith'.
As there are many other examples, I need a function that will seek out the
last ' ' in the cell content and then return all characters to the right
hand-side of this ' '.

e.g. 'Mr K Peters' would return 'Peters'

Please let me know if I can clarify!

Return cell characters after space
 

Brilliant, thanks!

"Jacob Skaria" wrote:

Hi Andy

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

--
Jacob (MVP - Excel)


"Andy" wrote:

I have a cell with 'Mr J. Smith' and want to return in another cell 'Smith'.
As there are many other examples, I need a function that will seek out the
last ' ' in the cell content and then return all characters to the right
hand-side of this ' '.

e.g. 'Mr K Peters' would return 'Peters'

Please let me know if I can clarify!