Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I retrieve the text string from the right of a cell
I have a list of names that include first (sometimes middle) and last names.
I need to just pull out the last names. The length is not constant so I cannot use the RIGHT function (as this only pulls the characters, not the string). Sometimes there is a middle name or intial, so I can't just count to the space (sometimes there are two spaces, or more). |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I retrieve the text string from the right of a cell
Try this =RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) The ONLY thing this won't do is extract people with a two word last name. -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=549985 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I retrieve the text string from the right of a cell
Try this:
For text in A1 This formula returns the text after the last space in A1... B1: =RIGHT(A1,LEN(A1)-LOOKUP(LEN(A1),FIND(" ",A1,ROW(INDEX($A:$A,1,1):INDEX($A:$A,LEN(A1),1))) )) Does that help? *********** Regards, Ron XL2002, WinXP "JWG" wrote: I have a list of names that include first (sometimes middle) and last names. I need to just pull out the last names. The length is not constant so I cannot use the RIGHT function (as this only pulls the characters, not the string). Sometimes there is a middle name or intial, so I can't just count to the space (sometimes there are two spaces, or more). |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I retrieve the text string from the right of a cell
=RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) That's perfect. Thank you. Sure, it also doesn't get it when someone uses the suffix "Jr" at the end, but it's better then I had. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I retrieve the text string from the right of a cell
On Thu, 8 Jun 2006 09:22:01 -0700, JWG wrote:
I have a list of names that include first (sometimes middle) and last names. I need to just pull out the last names. The length is not constant so I cannot use the RIGHT function (as this only pulls the characters, not the string). Sometimes there is a middle name or intial, so I can't just count to the space (sometimes there are two spaces, or more). You can use regular expressions to strip off the unwanted suffixes, and then extract the last name. Download and install Longre's free morefunc.xll add-in from http://xcell05.free.fr/ You can then use this formula: =REGEX.MID(REGEX.SUBSTITUTE(A1,"(Sr|Jr|I{2,3}|IV|M D|M\.D\.|PhD|\s+)$"),"\w+",-1) Note the mid portion of the formula: "(Sr|Jr|I{2,3}|IV|MD|M\.D\.|PhD)$" The bar-separated list between the parentheses is the list of unwanted suffixes. Periods have to be preceded by a slash, hence the M\.D\. for MD. the I{2,3} phrase will handle II or III Other suffixes can be added. The $ outside the parentheses signifies the end of the string, so that these suffixes will only be removed if they are at the end. Any trailing comma left after removing the suffixes will be ignored. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I convert text string into a cell reference | Excel Discussion (Misc queries) | |||
Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
dynamic cell reference within a text string | Excel Worksheet Functions | |||
Help inserting a Cell Value in a Text Cell | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |