Separating suffixes

[snipescc]

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>

 

[Joe4]

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.