Using Right Command based on number of characters? Maybe a different solution?

[TWTHOMAS]

I have a huge personnel data set that contains a tremendous number of employees.
Unfortunatley for me someone decided long ago that they would inlcude suffix's and sirnames and such to the middle inital without adding a space. Also some have a comma after the last name and others don't.



So I have names like

Johnson Ben AJR
Thomas, James EIII
Jones Trent R
Mitchell, Jaime

Anyone have a formula that will both remove the comma after the last name if there is one and also break the III's and JR's and Sr's and I's from the middle initial if they exist?


What I would like is results like this where each name part is in a different column with commas removed and the middle initial :

.......A.............B.............C..................D................
1.. Johnson..... Ben......... A................. JR
2.. Thomas..... James.......E ..................III
3.. Jones ........Trent........R
4.. Mitchell...... Jaime

Assuming all my names are in Column A and start at A2 can you give me a formula to do this?

 

[Special-K99]

Try (untested so make a backup)
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,", "," "),"JR"," JR"),"III"," III"),"SR"," SR")

This assumes there are no names that contain III, JR or SR.
Watch out for foreign names that may break this rule.

 

[Fluff]

If this is a once only event, you can use Text to Columns
1 Select column A Data > Text to Columns - Delimited > Next then check both Space & Comma then Finish
2 select column C Data > Text to Columns - Fixed Width > Next then click on the 1st division on the ruler so a vertical line appears after the 1st character & Finish

HTH

 

[Special-K99]

@Fluff: That wont separate the III, JRs or SRs from the initial letter as requested

 

[Rick Rothstein]

@Fluff: That wont separate the III, JRs or SRs from the initial letter as requested

The procedure Fluff laid out in his post worked for me (except for removing the possible comma after the last name which could be handled by using Excel's Replace dialog box). Perhaps you missed item #2 in his post?

 

[TWTHOMAS]

I used Special K99's formula and that worked prefect for splitting everything into just a space with no commas.

I am then able to use this to devide text to colums based on a formula.

=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),((COLUMNS($A1:A1)-1)*LEN($A1))+1,LEN($A1)))

This gives me the exact results I need! Thanks!

The reason I am not using text to columns the standard way is that I am building this as a 'drop the data into sheet 1' type thing to
create a mail merge for leangth of service awards. Way easier than teaching the clerk who prints them out how to use excel.

 

[Fluff]

The reason I am not using text to columns the standard way is that I am building this as a 'drop the data into sheet 1' type thing to
create a mail merge for leangth of service awards. Way easier than teaching the clerk who prints them out how to use excel.

Makes a lot of sense.
Thanks for the feedback