MoveMiddleInitial
 

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

MoveMiddleInitial
 

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

On Jun 14, 3:42 pm, gh wrote:
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

MoveMiddleInitial
 

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

"gh" wrote:

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

MoveMiddleInitial
 

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

"gh" wrote:

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

MoveMiddleInitial
 

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

"gh" wrote:

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

MoveMiddleInitial
 

HI,

Try using Data menu - Text to columns

Sassy

"gh" wrote:

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