formula for last names not working

[Stuepef]

I have a list of employee names that follow the Last name, First name Middle initial format. The formula I have works 95% of the time for employees with one last name, but it doesn't work when there is an employee that has two last names. I am looking to update my formula to accommodate the two last name employees:

Employee Name

GEORGIA GREEN,CHRISTINE A

WILLIAMS TATE,TIFFANY K

THOMPSON HILL,COURTNEY F

RISHARD,STEVEN T

DAVIS,JORDAN V

<tbody>
</tbody>

Desired Result:

CHRISTINE GEORGIA GREEN

TIFFANY WILLIAMS TATE

COURTNEY THOMPSON HILL

STEVEN RISHARD

JORDAN DAVIS

<tbody>
</tbody>

=IFERROR(MID(A1,FIND(",",A1)+1,FIND(" ",A1&" ")-FIND(",",A1)-1)&" "&LEFT(A1,FIND(",",A1)-1),"")

Thank you!

 

[Peter_SSs]

Try this one. (Not sure if you still need the IFERROR)

Excel Workbook
	A	B
1	Employee Name
2	GEORGIA GREEN,CHRISTINE A	CHRISTINE GEORGIA GREEN
3	WILLIAMS TATE,TIFFANY K	TIFFANY WILLIAMS TATE
4	THOMPSON HILL,COURTNEY F	COURTNEY THOMPSON HILL
5	RISHARD,STEVEN T	STEVEN RISHARD
6	DAVIS,JORDAN V	JORDAN DAVIS
Names

 

[Rick Rothstein]

Give this formula a try...

=MID(A1,FIND(",",A1)+1,LEN(A1)-FIND(",",A1)-2*(LEFT(RIGHT(A1,2))=" "))&" "&LEFT(A1,FIND(",",A1)-1)

 

[Rick Rothstein]

Spreadsheet Formulas

Cell Formula B2 =REPLACE(LEFT(A2,FIND(" ",A2,FIND(",",A2))),1,FIND(",",A2),"")& LEFT(A2,FIND(",",A2)-1) <tbody> </tbody>

<tbody>
</tbody>

Your formula does not work for my wife's name... she does not have a middle name, hence, no middle initial.

 

[Peter_SSs]

Your formula does not work for my wife's name... she does not have a middle name, hence, no middle initial.

Yes, posted wrong screen shot, since amended.

 

[Peter_SSs]

Your formula does not work for my wife's name... she does not have a middle name, hence, no middle initial.

.. and my amended formula also removes middle initials if there are two or more of them, either together or separated by spaces
DAVIS,JORDAN VA
DAVIS,JORDAN V A
DAVIS,JORDAN V A A

 

[Rick Rothstein]

.. and my amended formula also removes middle initials if there are two or more of them, either together or separated by spaces
DAVIS,JORDAN VA
DAVIS,JORDAN V A
DAVIS,JORDAN V A A

Which would mean it also remove the second name for people with a first name made up of two separate names. I worked with someone before retiring whose given first name was "Mary Ann" with the space... your formula changes her first name to "Mary".

 

[Peter_SSs]

Correct, but my solution is really directed at the OP's sample data. It could be that Jordan Davis does have 2 first names and that however the data is generated automatically cuts it off that way. Unless the OP tells us otherwise, all we can really do is go with the samples & make any other reasonable assumptions that we can - as we both have done.

 

[Stuepef]

@Peter_SSs and @Rick Rothstein - thank you!

 

[Peter_SSs]

@Peter_SSs and @Rick Rothstein - thank you!

You are very welcome.