MoveMiddleInitial

G

gh

I have a column with the Middle Initial and Lastname(L Doe). How do I
copy the middle initial and lastname, to seperate columns? I was going
to get the leftmost characeter, but some of the cells only have the
lastname in them, there is no middle initial.

Thanks
 
V

vezerid

In B2, to get the middle initial (or leave blank if no MI):

=IF(ISNUMBER(FIND(" ",A2)),LEFT(A2,FIND(" ",A2)-1),"")

A simpler version, since always MI will have at most 1 character:
=IF(ISNUMBER(FIND(" ",A2)),LEFT(A2,1),"")

In C2, for the last name:

=IF(ISNUMBER(FIND(" ",A2)),MID(A2,FIND(" ",A2)+1,LEN(A2)),A2)

Simpler:
=IF(ISNUMBER(FIND(" ",A2)),MID(A2,3,LEN(A2)),A2)

HTH
Kostis Vezerides
 
T

Toppers

try:

=IF(ISNUMBER(FIND(" ",A1)),MID(A1,FIND(" ",A1)+1,255),A1)

Name in A1: assumes one blank between Middle (if it exists) & Lastname

HTH
 
D

Duke Carey

Make sure the column to the right is empty - insert a new column if needed.
Select the column of data
Use Data->Text to columns->Delimited->Space
Excel will split the two values into separate columns
 
M

Mike H

I'm confused.

If the column contains the Middle initial and last name and you want to copy
both, what do you want to leave behind or not copy?

Mike
 
Top