Excel Edit/replace command

D

Donna

I'm attempting to reformat:
Donna R Yager
in column A

to:
Donna R
in column A
and
Yager
in column B

I know I can insert a new column duplicate the full name in columns a and B
and then use the space* in the Find Field and Replace with 'blank' and vice
versa to get the first and last names deleted from each column so that I end
up with First name in column A and Last name in Column B.

EXCEPT I cannot seem to get the Middle Initial to remain in Column A

anyone know what format I must use, or formula that will work? Obviously
the full names are not all the same length.
 
B

Bob I

For this example the Full name will be in A, First Name and Initial will
be in Column B, and Last name in Column C

So A = Donna R Yager

B contains =LEFT(A1,SEARCH(" ",A1,1)+1) resulting in

"Donna R"

C contains =MID(A1,SEARCH(" ",A1,1)+3,LEN(A1)-SEARCH(" ",A1,1)+2)
resulting in

"Yager"

After this is done you may Copy, Paste Special, Values to A and B , OR
to B and C and then Delete column A.
 
B

Bob I

This is very basic example as it only looks for the first space
character, and counts over from there. IF there is 2 spaces before the
Middle initial then it will not show the Initial but a space.You would
need to correct the list (remove double spacing) first.
 
Top