First Name Middle Initial No Space

[Jenawade]

Now that someone decided that most of our employee names in our project managment system will show up as "Doe JohnC" and "Doe JaneB" while a rare few still follow more normal conventions like "Smith, Joe" and "Taylor, John B" I spend a lot of time manually changing these names.

I used to use Text to Columns with space, period, comma as my dividers (some middle initials had a period after them, some didn't, and also some names have a Jr. or II or III in there) and then Concatenate to line up all my names for use in VLookup to compare this data with other employee lists that show names as "Doe, John" and "Smith, Joe."

Right now I manually click each cell to delete the capitalized middle initial (usually tacked right on to the first name with no space) and then use Text to Columns and Concatenate to cover the inconsistent use of a comma.

Is there a way that I can still have Excel pull out the middle initial for me? It is not on all names, so it's not just a matter of taking out the last character. If it's capitalized, it's the middle initial, like: Doe JohnC and Doe JaneB.

Thanks!

 

[fairwinds]

Hi,

Try:

=IF(EXACT(RIGHT(A1),UPPER(RIGHT(A1))),RIGHT(A1),"")

 

[Jenawade]

Oops, I see I wasn't clear on what I wanted. =IF(EXACT(RIGHT(A1),UPPER(RIGHT(A1))),RIGHT(A1),"") works great to give me the middle initials, but what I need is the opposite, the first and last name without the middle initial included.

 

[barry houdini]

You just need a tweak to Fairwind's suggestion...

=LEFT(A1,LEN(A1)-EXACT(RIGHT(A1),UPPER(RIGHT(A1))))