Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move last names to front
With James William Ravenswood in A1
=RIGHT(A1,LEN(A1)-FIND(" ",A1,FIND(" ",A1,1)+1)) & ", " & LEFT(A1,FIND(" ",A1,FIND(" ",A1,1)+1)) will display Ravenswood, James William -- Gary''s Student - gsnu200853 "John" wrote: I have a large sheet of rows of names and addresses that lists names in the order: firstname middlename lastname What is a quick way to change all entries in the names column to lastname, firstname middlename so I can sort the list by last name. I appreciate your help, -John |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move last names to front
This only works w/ 2 word names. Turns out I have serveral in list who have
multiple middle names. What I need to do is pick off the last name and move it to the front followed by the comma and the remaining string w/ the last name removed. How do I extract the last word in a character string? I appreciate your help, -John "Gary''s Student" wrote: With James William Ravenswood in A1 =RIGHT(A1,LEN(A1)-FIND(" ",A1,FIND(" ",A1,1)+1)) & ", " & LEFT(A1,FIND(" ",A1,FIND(" ",A1,1)+1)) will display Ravenswood, James William -- Gary''s Student - gsnu200853 "John" wrote: I have a large sheet of rows of names and addresses that lists names in the order: firstname middlename lastname What is a quick way to change all entries in the names column to lastname, firstname middlename so I can sort the list by last name. I appreciate your help, -John |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move last names to front
In B1 enter:
=SEARCH("|",SUBSTITUTE(A1," ","|",(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) In C1 enter: =RIGHT(A1,LEN(A1)-B1) & ", " & LEFT(A1,B1-1) If A1 contains: Juan Carlos De Borbon Y Borbon Then B1 displays: 24 and C1 displays: Borbon, Juan Carlos De Borbon Y -- Gary''s Student - gsnu200853 "John" wrote: This only works w/ 2 word names. Turns out I have serveral in list who have multiple middle names. What I need to do is pick off the last name and move it to the front followed by the comma and the remaining string w/ the last name removed. How do I extract the last word in a character string? I appreciate your help, -John "Gary''s Student" wrote: With James William Ravenswood in A1 =RIGHT(A1,LEN(A1)-FIND(" ",A1,FIND(" ",A1,1)+1)) & ", " & LEFT(A1,FIND(" ",A1,FIND(" ",A1,1)+1)) will display Ravenswood, James William -- Gary''s Student - gsnu200853 "John" wrote: I have a large sheet of rows of names and addresses that lists names in the order: firstname middlename lastname What is a quick way to change all entries in the names column to lastname, firstname middlename so I can sort the list by last name. I appreciate your help, -John |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move last names to front
This works much better:
=TRIM(RIGHT(SUBSTITUTE(B6," ",REPT(" ",99)),99)) & ", " & TRIM(SUBSTITUTE(B6,TRIM(RIGHT(SUBSTITUTE(B6," ",REPT(" ",99)),99)),"")) "Gary''s Student" wrote: In B1 enter: =SEARCH("|",SUBSTITUTE(A1," ","|",(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) In C1 enter: =RIGHT(A1,LEN(A1)-B1) & ", " & LEFT(A1,B1-1) If A1 contains: Juan Carlos De Borbon Y Borbon Then B1 displays: 24 and C1 displays: Borbon, Juan Carlos De Borbon Y -- Gary''s Student - gsnu200853 "John" wrote: This only works w/ 2 word names. Turns out I have serveral in list who have multiple middle names. What I need to do is pick off the last name and move it to the front followed by the comma and the remaining string w/ the last name removed. How do I extract the last word in a character string? I appreciate your help, -John "Gary''s Student" wrote: With James William Ravenswood in A1 =RIGHT(A1,LEN(A1)-FIND(" ",A1,FIND(" ",A1,1)+1)) & ", " & LEFT(A1,FIND(" ",A1,FIND(" ",A1,1)+1)) will display Ravenswood, James William -- Gary''s Student - gsnu200853 "John" wrote: I have a large sheet of rows of names and addresses that lists names in the order: firstname middlename lastname What is a quick way to change all entries in the names column to lastname, firstname middlename so I can sort the list by last name. I appreciate your help, -John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Move line to front | Charts and Charting in Excel | |||
Move line to front | Charts and Charting in Excel | |||
How do I swap a group of surnames to the front of first names? | Excel Discussion (Misc queries) | |||
How do I move a hidden bubble to the front in a bubble chart in E. | Excel Discussion (Misc queries) | |||
move '-' from back to front??? | Excel Programming |