MoveMiddleInitial

[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

 

[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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

HI,

Try using Data menu - Text to columns

Sassy