Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pull Out Last Name
Hi:
I am looking for a function in Excel to extract the LAST NAME from a cell that looks like: Justin Kenney I want the formula to give me only the last name (ie Kenney) Can anyone help with a formual (maybe the RIGHT function) that looks RIGHT TO LEFT for the 1st space...and then gives me everything to the RIGHT of the space Thanks Warren |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pull Out Last Name
One way:
=TRIM(MID(A1,FIND(" ",A1&" "),255)) Biff "WDP" wrote in message ... Hi: I am looking for a function in Excel to extract the LAST NAME from a cell that looks like: Justin Kenney I want the formula to give me only the last name (ie Kenney) Can anyone help with a formual (maybe the RIGHT function) that looks RIGHT TO LEFT for the 1st space...and then gives me everything to the RIGHT of the space Thanks Warren |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pull Out Last Name
WDP wrote:
Hi: I am looking for a function in Excel to extract the LAST NAME from a cell that looks like: Justin Kenney I want the formula to give me only the last name (ie Kenney) Can anyone help with a formual (maybe the RIGHT function) that looks RIGHT TO LEFT for the 1st space...and then gives me everything to the RIGHT of the space Thanks Warren Hi Warren, I think you can use this formula: =RIGHT(A1,LEN(A1)-FIND("[",SUBSTITUTE(A1," ","[",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) that take in account the possibility to have more than one space per cell. If you are sure that there is just one space per cell you can use this one: =RIGHT(A1,LEN(A1)-FIND(" ",A1)) -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pull Out Last Name
On Thu, 24 Aug 2006 18:17:01 -0700, WDP wrote:
Hi: I am looking for a function in Excel to extract the LAST NAME from a cell that looks like: Justin Kenney I want the formula to give me only the last name (ie Kenney) Can anyone help with a formual (maybe the RIGHT function) that looks RIGHT TO LEFT for the 1st space...and then gives me everything to the RIGHT of the space Thanks Warren This formula will pull out everything after the last space in the string: =MID(A1,1+FIND(CHAR(1),SUBSTITUTE(A1," ", CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ","")))),255) If there might be a trailing <space in the string, then use: =MID(TRIM(A1),1+FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ", CHAR(1),LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ","")))),255) --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
hyper links in pull down menus | Excel Discussion (Misc queries) | |||
Personal macro file will not pull up when excel is started | Setting up and Configuration of Excel | |||
Referencing multiple criteria to pull data | Excel Discussion (Misc queries) | |||
Not all data in AutoFilter pull down | Excel Worksheet Functions | |||
Why does AutoFilter not show all the data in the pull down? | Excel Discussion (Misc queries) |