First Name Middle Initial No Space

Jenawade

Board Regular
Joined
Apr 8, 2002
Messages
231
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!
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Jenawade

Board Regular
Joined
Apr 8, 2002
Messages
231
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
You just need a tweak to Fairwind's suggestion...

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

Watch MrExcel Video

Forum statistics

Threads
1,133,652
Messages
5,660,138
Members
418,552
Latest member
Idiocracy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top