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!!!
>
>
|
Similar Threads
