Home |
Search |
Today's Posts |
#1
|
|||
|
|||
extracting last name
I have names of individuals in Column A. Some have only first and last,
others also include the middle initial. I want to extract the last name of each person into Column B. I used the formula =RIGHT(A1,LEN(A1)-SEARCH(" ",A1)) which works great for those names that don't include the middle initial. How can I modify this to work for either situation? Thanks!!! -- maryj |
#2
|
|||
|
|||
=MID(A1,LOOKUP(2,1/(MID(" "&A1,ROW(INDIRECT("1:1024")),1)="
"),ROW(INDIRECT("1:1024"))),1024) "maryj" wrote in message ... I have names of individuals in Column A. Some have only first and last, others also include the middle initial. I want to extract the last name of each person into Column B. I used the formula =RIGHT(A1,LEN(A1)-SEARCH(" ",A1)) which works great for those names that don't include the middle initial. How can I modify this to work for either situation? Thanks!!! -- maryj |
#3
|
|||
|
|||
Try this with the nhame in D2
=IF(ISERR(FIND(" ",D2)),D2,MID(D2,FIND("^^",SUBSTITUTE(D2," ","^^",LEN(D2)-LEN(SUBSTITUTE(D2," ",""))))+1,1024)) -- Regards Ron de Bruin http://www.rondebruin.nl "maryj" wrote in message ... I have names of individuals in Column A. Some have only first and last, others also include the middle initial. I want to extract the last name of each person into Column B. I used the formula =RIGHT(A1,LEN(A1)-SEARCH(" ",A1)) which works great for those names that don't include the middle initial. How can I modify this to work for either situation? Thanks!!! -- maryj |
#4
|
|||
|
|||
Try...
=RIGHT(A1,LEN(A1)-SEARCH("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) Hope this helps! In article , "maryj" wrote: I have names of individuals in Column A. Some have only first and last, others also include the middle initial. I want to extract the last name of each person into Column B. I used the formula =RIGHT(A1,LEN(A1)-SEARCH(" ",A1)) which works great for those names that don't include the middle initial. How can I modify this to work for either situation? Thanks!!! |
#5
|
|||
|
|||
Thanks Ron - that worked! Can you explain what the ^^ mean and why the 1024
at the end? -- maryj "Ron de Bruin" wrote: Try this with the nhame in D2 =IF(ISERR(FIND(" ",D2)),D2,MID(D2,FIND("^^",SUBSTITUTE(D2," ","^^",LEN(D2)-LEN(SUBSTITUTE(D2," ",""))))+1,1024)) -- Regards Ron de Bruin http://www.rondebruin.nl "maryj" wrote in message ... I have names of individuals in Column A. Some have only first and last, others also include the middle initial. I want to extract the last name of each person into Column B. I used the formula =RIGHT(A1,LEN(A1)-SEARCH(" ",A1)) which works great for those names that don't include the middle initial. How can I modify this to work for either situation? Thanks!!! -- maryj |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting info from word and displaying in an excel spreadsheet | Excel Discussion (Misc queries) | |||
Extracting File Names and Properties | Excel Worksheet Functions | |||
Extracting data from other columns | Excel Worksheet Functions | |||
Extracting a value from between brackets | Excel Discussion (Misc queries) | |||
Extracting Values on one list and not another | Excel Discussion (Misc queries) |