<div>
Keeping all three you might try<br />
<br />
=PROPER((RIGHT(A2,LEN(A2)-<wbr />FIND(&quot;, &quot;,A2,1)-1))&amp;&quot; &quot;&amp;(LEFT(A2,FIND(&quot;, &quot;,A2,1)-1)))
</div>


Keeping all three you might try

=PROPER((RIGHT(A2,LEN(A2)-FIND(", ",A2,1)-1))&" "&(LEFT(A2,FIND(", ",A2,1)-1)))

<div>
This did the trick as requested.
</div>


<div>
<div class="content wysiwyg-content">
I am currently using the following formula to take names on a spreadsheet from <i>Last, First</i> to <i>First Last</i>:<br />
<br />
=PROPER((RIGHT(A2,LEN(A2)-<wbr />FIND(&quot; &quot;,A2,1)))&amp;&quot; &quot;&amp;(LEFT(A2,FIND(&quot; &quot;,A2,1)-2)))<br />
<br />
Recently we've had a few names show up with Jr. or a middle name on the end appearing as such:<br />
<br />
<i>Doe JR, John</i> or <i>Doe Middle, John</i> rather than just <i>Doe, John</i><br />
<br />
This causes my formula to go a little bananas. &nbsp;Rather than use ctrl+H and replace them, I would like to try and make some changes to the above formula to do it all in once pass if possible. &nbsp;How would I go about making the above formula ignore the third word as they appear in the examples?<br />
<br />
Thanks!<br />
<br />
-Straws
</div>
</div>


I am currently using the following formula to take names on a spreadsheet from Last, First to First Last:

=PROPER((RIGHT(A2,LEN(A2)-FIND(" ",A2,1)))&" "&(LEFT(A2,FIND(" ",A2,1)-2)))

Recently we've had a few names show up with Jr. or a middle name on the end appearing as such:

Doe JR, John or Doe Middle, John rather than just Doe, John

This causes my formula to go a little bananas.  Rather than use ctrl+H and replace them, I would like to try and make some changes to the above formula to do it all in once pass if possible.  How would I go about making the above formula ignore the third word as they appear in the examples?

Thanks!

-Straws

Removing a name suffix (JR) and a middle name from a text box in Excel 2010

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Microsoft Excel

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.