Remove Middle Initial from list of names with formula

Stuepef

Board Regular
Joined
Oct 23, 2017
Messages
128
Office Version
  1. 2021
Platform
  1. Windows
I have list of names in the format of Last Name, First Name Middle Initial. The problem is not every single name has a middle initial and I am looking to make a formula to put the names in First Name Last Name format. Here is the list and the formula I have so far:

Extract first name formula:
Code:
=MID(A1,FIND(",",A1)+1,LEN(A1))

Extract last name formula:
Code:
=LEFT(A1,FIND(",",A1)-1)

List of names:
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]WILLIS,GEORGE A[/TD]
[/TR]
[TR]
[TD]GREEN,RICK[/TD]
[/TR]
[TR]
[TD]THOMAS-MATTS,CHARLOTTE R[/TD]
[/TR]
[TR]
[TD]PEPPERS,BONNIE[/TD]
[/TR]
[TR]
[TD]MONTEL,DWIGHT F[/TD]
[/TR]
[TR]
[TD]HARVER,JORDAN[/TD]
[/TR]
</tbody>[/TABLE]

Desired Result:
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]GEORGE WILLIS[/TD]
[/TR]
[TR]
[TD]RICK GREEN[/TD]
[/TR]
[TR]
[TD]CHARLOTTE THOMAS-MATTS[/TD]
[/TR]
[TR]
[TD]BONNIE PEPPERS[/TD]
[/TR]
[TR]
[TD]DWIGHT MONTEL[/TD]
[/TR]
[TR]
[TD]JOARDAN HARVER[/TD]
[/TR]
</tbody>[/TABLE]

Thank you for the assistance!
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
1. Highlight your column
2. Go to the data tab in the ribbon
3. Select "Text to Columns"
4. Select "Delimited"
5. Select "Next"
6. Deselect "Tab" & Select "Space"
7. Select "Next"
8. Set your "Destination" as the next blank column in your spreadsheet
9. Select "Finish"

This will separate the middle initial from the rest of the names. Delete the Middle initial column that gets created. Then you will be able to use your formulas to extract first and last names.
 
Upvote 0
If you want formula then,


Book1
AB
1NameFirst Name Last Name
2WILLIS,GEORGE AGEORGE WILLIS
3GREEN,RICKRICK GREEN
4THOMAS-MATTS,CHARLOTTE RCHARLOTTE THOMAS-MATTS
5PEPPERS,BONNIEBONNIE PEPPERS
6MONTEL,DWIGHT FDWIGHT MONTEL
7HARVER,JORDANJORDAN HARVER
Sheet4
Cell Formulas
RangeFormula
B2=TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(A2," ",REPT(" ",500)),500)),",",REPT(" ",100)),100) &" "& LEFT(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(A2," ",REPT(" ",500)),500)),",",REPT(" ",100)),100))
 
Upvote 0
I like that functionality within excel, but a formula is the method I would like to use to solve this particular instance.
 
Upvote 0
Or,


Book1
AB
1NameFirst Name Last Name
2WILLIS,GEORGE AGEORGE WILLIS
3GREEN,RICKRICK GREEN
4THOMAS-MATTS,CHARLOTTE RCHARLOTTE THOMAS-MATTS
5PEPPERS,BONNIEBONNIE PEPPERS
6MONTEL,DWIGHT FDWIGHT MONTEL
7HARVER,JORDANJORDAN HARVER
Sheet4
Cell Formulas
RangeFormula
B2=MID(A2,FIND(",",A2)+1,FIND(" ",A2&" ")-FIND(",",A2)-1)&" "&LEFT(A2,FIND(",",A2)-1)
 
Upvote 0

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

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