formula for last names not working

Stuepef

Board Regular
Joined
Oct 23, 2017
Messages
128
Office Version
  1. 2021
Platform
  1. Windows
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:

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Employee Name[/TD]
[/TR]
[TR]
[TD]GEORGIA GREEN,CHRISTINE A[/TD]
[/TR]
[TR]
[TD]WILLIAMS TATE,TIFFANY K[/TD]
[/TR]
[TR]
[TD]THOMPSON HILL,COURTNEY F[/TD]
[/TR]
[TR]
[TD]RISHARD,STEVEN T[/TD]
[/TR]
[TR]
[TD]DAVIS,JORDAN V[/TD]
[/TR]
</tbody>[/TABLE]

Desired Result:
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]CHRISTINE GEORGIA GREEN[/TD]
[/TR]
[TR]
[TD]TIFFANY WILLIAMS TATE[/TD]
[/TR]
[TR]
[TD]COURTNEY THOMPSON HILL[/TD]
[/TR]
[TR]
[TD]STEVEN RISHARD[/TD]
[/TR]
[TR]
[TD]JORDAN DAVIS[/TD]
[/TR]
</tbody>[/TABLE]

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

Thank you!
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Try this one. (Not sure if you still need the IFERROR)


Excel Workbook
AB
1Employee Name
2GEORGIA GREEN,CHRISTINE ACHRISTINE GEORGIA GREEN
3WILLIAMS TATE,TIFFANY KTIFFANY WILLIAMS TATE
4THOMPSON HILL,COURTNEY FCOURTNEY THOMPSON HILL
5RISHARD,STEVEN TSTEVEN RISHARD
6DAVIS,JORDAN VJORDAN DAVIS
Names
 
Last edited:
Upvote 0
Give this formula a try...

=MID(A1,FIND(",",A1)+1,LEN(A1)-FIND(",",A1)-2*(LEFT(RIGHT(A1,2))=" "))&" "&LEFT(A1,FIND(",",A1)-1)
 
Upvote 0
Spreadsheet Formulas
CellFormula
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.
 
Last edited:
Upvote 0
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
 
Last edited:
Upvote 0
.. 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".
 
Upvote 0
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. :)
 
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,260
Members
452,627
Latest member
KitkatToby

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