Separating suffixes

snipescc

Board Regular
Joined
Aug 29, 2010
Messages
130
Hi everyone. I have a giant list people, and am having a problem with cleaning the data and making it uniform. The issue is that there is supposed to be a column that has name suffixes (Jr, Sr, iii ect), but many of the names have the suffix after the last name. I am trying to move them over to the correct coulmn. I have figured out a way of moving over whatever comes after the first space (By having a column counting the distance to the first space, and another the legth of the whole cell, then one to the right and one to the left of that space) The problem is that many people have two last names, and sometimes there is a space between them. Anyone have any ideas? This is about what the data looks like. Any help is appreciated.

<table width="373" border="0" cellpadding="0" cellspacing="0"><col style="width: 152pt;" width="202"> <col style="width: 48pt;" width="64" span="2"> <col style="width: 32pt;" width="43"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 152pt;" width="202" height="20">Last Name</td> <td style="width: 48pt;" width="64">First Name</td> <td style="width: 48pt;" width="64">Middle Name</td> <td style="width: 32pt;" width="43">Suffix</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Smith</td> <td>John</td> <td>W</td> <td>Jr
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Smith Jr
</td> <td>John
</td> <td>A</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Jones -Smith
</td> <td>Sarah
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Jones -Smith Jr
</td> <td>Frank</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> </tr> </tbody></table>
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
How about doing some Search & Replaces first?

Replace "- " (dash space) with "-" dash
and
Replace " -" (space dash) with "-" dash

That should remove all the spaces from the hyphenated last names, then your logic should work.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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