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 |
=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 |
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 |
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!!! |
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 |
All times are GMT +1. The time now is 12:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com