Remove middle initial and Jr, if it exists

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hey all,

Coffee hasn't hit my brain yet this morning and I cant figure this one out.

I have a list of names that are in the following format:

LastName Suffix, FirstName MI

The middle initial is not always present, neither is the suffix. For simplicity purposes, we will assume the suffix can ONLY be "Jr."

Here is some sample data:

Brown Jr., Wayne
Fowler, Michael
Parker, David A
Winstead Jr., Johnny N

I want the output to be the following:

Brown, Wayne
Fowler, Michael
Parker, David
Winstead, Johnny

Thanks in advance for the help!

PS (if i can get a formula that at least gets rid of the middle initial at the end (if it exists), then that would be a HUGE step in the right direction, as the Jr. only occurs on maybe 3% of my data. The middle initial is in about 80% of the data.
 
Last edited:

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
This formula should do it.
=SUBSTITUTE(IF(LEFT(RIGHT(A1,2),1)=" ",LEFT(A1,LEN(A1)-2),A1)," Jr.","")
 
Upvote 0
Very awesome!

That works great!

However, i just came across a problem I did not foresee.

The data sent to me occasionally has it as the following:

Young Jr., Stephen
Powers, Jr., Austin
Ru****i , Robert

Notice the extra "," and the extra " " there. >.<
 
Last edited:
Upvote 0
Try this:
=LEFT(A1,(MIN(FIND(" ",A1),FIND(",",A1)))-1)&", "&IF(RIGHT(A1,2)=RIGHT(SUBSTITUTE(A1," ",""),2),(RIGHT(A1,LEN(A1)-FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))),(LEFT((RIGHT(A1,LEN(A1)-FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1)))),FIND(" ",(RIGHT(A1,LEN(A1)-FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1)))))-1)))


It may be huge, but that works GREAT! Thank you!
 
Upvote 0
This will deal with those inconsistancies.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(LEFT(RIGHT(A1,2),1)=" ",LEFT(A1,LEN(A1)-2),A1)," Jr.",""),",,",",")," ,",",")
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

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
Back
Top