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