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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

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))))
 

Forum statistics

Threads
1,136,954
Messages
5,678,759
Members
419,782
Latest member
gc75150

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