Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I resort names
This is stumping me - I have a large database list that has multiple
names listed with Last Name first, First Name and Middile Initial last (if availiable) then backslash 2nd or more names - same format as 1st name - all w/out commas. How would I do following - Eliminate all but the first named and reformat to First Name, Last Name and no MI - Example - BEHRENS LAWRENCE W/JEANNE SEDELLO/E - Reformatted to - LAWRENCE BEHRENS. I assume it would take mutiple steps - elimination, comma entry and name reformat as at this point I am going to have to manually eliminate the multiple names then apply formulas. Any assitance would be greatly appriciated!!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I resort names
Presumes your names to be rebuilt are in column A, and we're working on Row 1:
'=IF(ISERROR(FIND(" ",A1)),A1,MID(TRIM(A1) & " ",FIND(" ",TRIM(A1) & " ")+1,FIND(" ",TRIM(A1) & " ",FIND(" ",TRIM(A1) & " ")+1)-FIND(" ",TRIM(A1) & " ")) & LEFT(TRIM(A1) & " ",FIND(" ",TRIM(A1) & " ")-1)) This is very much built around the rules you laid out. In the end all we have to do is isolate the first two groups of characters (first name, last name) and turn them around. I have allowed for a situation where there is just a single name such as Twiggy or Prince or Madonna also. What this does is look for the position of the first space character, and based on it, also looks for the location of the second one. The TRIM() function trims any leading or trailing white-space first, then the & " " portion adds a space back to the end of it so that there is always at least one space to be found. The thing that could cause an error would be a double-space between the original last name and first name. I haven't tested this under all conditions, like with the doublespace or an empty cell, but given your BEHRENS example, it works. "dsrtdrms" wrote: This is stumping me - I have a large database list that has multiple names listed with Last Name first, First Name and Middile Initial last (if availiable) then backslash 2nd or more names - same format as 1st name - all w/out commas. How would I do following - Eliminate all but the first named and reformat to First Name, Last Name and no MI - Example - BEHRENS LAWRENCE W/JEANNE SEDELLO/E - Reformatted to - LAWRENCE BEHRENS. I assume it would take mutiple steps - elimination, comma entry and name reformat as at this point I am going to have to manually eliminate the multiple names then apply formulas. Any assitance would be greatly appriciated!!! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I resort names
Assuming your list in column A, put this formula in B1 and copy down........
=MID(A1,FIND(" ",A1,1)+1,FIND(" ",A1,1)+1)&LEFT(A1,FIND(" ",A1,1)-1) Vaya con Dios, Chuck, CABGx3 "dsrtdrms" wrote: This is stumping me - I have a large database list that has multiple names listed with Last Name first, First Name and Middile Initial last (if availiable) then backslash 2nd or more names - same format as 1st name - all w/out commas. How would I do following - Eliminate all but the first named and reformat to First Name, Last Name and no MI - Example - BEHRENS LAWRENCE W/JEANNE SEDELLO/E - Reformatted to - LAWRENCE BEHRENS. I assume it would take mutiple steps - elimination, comma entry and name reformat as at this point I am going to have to manually eliminate the multiple names then apply formulas. Any assitance would be greatly appriciated!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Duplicate Range Names by worksheet | Excel Discussion (Misc queries) | |||
The filter didn't show all (2,254) names when I set it up | Excel Discussion (Misc queries) | |||
Convert Local Names to Global Names | Excel Worksheet Functions | |||
sorting names alphabetically when names may start with numbers | Excel Discussion (Misc queries) | |||
How can I find the common names in two columns of names? | Excel Discussion (Misc queries) |