| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find And Display Surname
I'm sure there's an easy way to do this, but I can't find it! I've
got names in cells like this: MRS EDNA JOAN PASCOE (could be any number of first and middle names). In other corresponding cells I want to display only the surname. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find And Display Surname
On Sat, 24 May 2008 12:03:23 -0700 (PDT), robzrob wrote:
I'm sure there's an easy way to do this, but I can't find it! I've got names in cells like this: MRS EDNA JOAN PASCOE (could be any number of first and middle names). In other corresponding cells I want to display only the surname. This formula will return the last word in a string: =MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1), LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255) --ron |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find And Display Surname
On May 24, 8:40*pm, Ron Rosenfeld wrote:
On Sat, 24 May 2008 12:03:23 -0700 (PDT), robzrob wrote: I'm sure there's an easy way to do this, but I can't find it! *I've got names in cells like this: *MRS EDNA JOAN PASCOE (could be any number of first and middle names). *In other corresponding cells I want to display only the surname. This formula will return the last word in a string: =MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1), LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255) --ron Excellent, Ron, thank you. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find And Display Surname
I'm sure there's an easy way to do this, but I can't find it! I've
got names in cells like this: MRS EDNA JOAN PASCOE (could be any number of first and middle names). In other corresponding cells I want to display only the surname. This formula will return the last word in a string: =MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1), LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255) For those who might be interested, below is a shorter array-entered** formula that does the same thing. It uses less function calls and does not produce an error when the referenced cell is empty. =MID(A1,MAX((MID(A1,ROW(1:255),1)=" ")*ROW(1:255))+1,255) ** For the archives: Use Ctrl+Shift+Enter to commit this formula, not Enter by itself. Rick |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find And Display Surname
On Sun, 25 May 2008 15:10:29 -0400, "Rick Rothstein \(MVP - VB\)"
wrote: =MID(A1,MAX((MID(A1,ROW(1:255),1)=" ")*ROW(1:255))+1,255) If you make the ROW references absolute, then you'll be able to copy the formula by dragging: =MID(A1,MAX((MID(A1,ROW($1:$255),1)=" ")*ROW($1:$255))+1,255) --ron |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find And Display Surname
=MID(A1,MAX((MID(A1,ROW(1:255),1)=" ")*ROW(1:255))+1,255)
If you make the ROW references absolute, then you'll be able to copy the formula by dragging: =MID(A1,MAX((MID(A1,ROW($1:$255),1)=" ")*ROW($1:$255))+1,255) Good point. Rick |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find And Display Surname
And an even *shorter, non-array* approach:
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)) -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Rick Rothstein (MVP - VB)" wrote in message ... I'm sure there's an easy way to do this, but I can't find it! I've got names in cells like this: MRS EDNA JOAN PASCOE (could be any number of first and middle names). In other corresponding cells I want to display only the surname. This formula will return the last word in a string: =MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1), LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255) For those who might be interested, below is a shorter array-entered** formula that does the same thing. It uses less function calls and does not produce an error when the referenced cell is empty. =MID(A1,MAX((MID(A1,ROW(1:255),1)=" ")*ROW(1:255))+1,255) ** For the archives: Use Ctrl+Shift+Enter to commit this formula, not Enter by itself. Rick |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find And Display Surname
I like it! One function call less than mine and it still doesn't error out
if the reference cell is empty. Of course, if the last word is longer than 99 characters...<g Rick "Ragdyer" wrote in message ... And an even *shorter, non-array* approach: =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)) -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Rick Rothstein (MVP - VB)" wrote in message ... I'm sure there's an easy way to do this, but I can't find it! I've got names in cells like this: MRS EDNA JOAN PASCOE (could be any number of first and middle names). In other corresponding cells I want to display only the surname. This formula will return the last word in a string: =MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1), LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255) For those who might be interested, below is a shorter array-entered** formula that does the same thing. It uses less function calls and does not produce an error when the referenced cell is empty. =MID(A1,MAX((MID(A1,ROW(1:255),1)=" ")*ROW(1:255))+1,255) ** For the archives: Use Ctrl+Shift+Enter to commit this formula, not Enter by itself. Rick |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find And Display Surname
I liked it too, the first time I saw it.
Wish I could remember who to credit it to. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Rick Rothstein (MVP - VB)" wrote in message ... I like it! One function call less than mine and it still doesn't error out if the reference cell is empty. Of course, if the last word is longer than 99 characters...<g Rick "Ragdyer" wrote in message ... And an even *shorter, non-array* approach: =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)) -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Rick Rothstein (MVP - VB)" wrote in message ... I'm sure there's an easy way to do this, but I can't find it! I've got names in cells like this: MRS EDNA JOAN PASCOE (could be any number of first and middle names). In other corresponding cells I want to display only the surname. This formula will return the last word in a string: =MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1), LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255) For those who might be interested, below is a shorter array-entered** formula that does the same thing. It uses less function calls and does not produce an error when the referenced cell is empty. =MID(A1,MAX((MID(A1,ROW(1:255),1)=" ")*ROW(1:255))+1,255) ** For the archives: Use Ctrl+Shift+Enter to commit this formula, not Enter by itself. Rick |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Change around surname and name | Excel Discussion (Misc queries) | |||
| Extract given and surname a string | Excel Worksheet Functions | |||
| Surname and First names formula | Excel Worksheet Functions | |||
| Splitting firstName from Surname | Excel Worksheet Functions | |||
| Display only surname | Excel Worksheet Functions | |||
Hybrid Mode
