Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 414
Default 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!
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up 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.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,520
Default 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!

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 414
Default 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!

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
return value based on number of characters in cell / field Perplexed Excel Worksheet Functions 4 December 10th 08 09:48 PM
Trim Characters Other Than An Empty Space At The End Of A Cell K8_Dog Excel Worksheet Functions 5 April 19th 08 01:29 AM
Count Characters with space in a cell NH Excel Discussion (Misc queries) 5 April 5th 07 05:07 AM
cHARACTERS BEFORE THE SPACE T De Villiers Excel Worksheet Functions 3 January 19th 06 02:22 AM
If it's not a space, return the cell contents. Gee... Excel Worksheet Functions 3 January 4th 06 12:50 AM


All times are GMT +1. The time now is 10:00 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"