Remove Middle Initial from list of names with formula

[Stuepef]

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:

=MID(A1,FIND(",",A1)+1,LEN(A1))

Extract last name formula:

=LEFT(A1,FIND(",",A1)-1)

List of names:

WILLIS,GEORGE A

GREEN,RICK

THOMAS-MATTS,CHARLOTTE R

PEPPERS,BONNIE

MONTEL,DWIGHT F

HARVER,JORDAN

<tbody>
</tbody>

Desired Result:

GEORGE WILLIS

RICK GREEN

CHARLOTTE THOMAS-MATTS

BONNIE PEPPERS

DWIGHT MONTEL

JOARDAN HARVER

<tbody>
</tbody>

Thank you for the assistance!

 

[Captain_Conman]

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.

 

[Nishant94]

If you want formula then,

Book1
	A	B
1	Name	First Name Last Name
2	WILLIS,GEORGE A	GEORGE WILLIS
3	GREEN,RICK	RICK GREEN
4	THOMAS-MATTS,CHARLOTTE R	CHARLOTTE THOMAS-MATTS
5	PEPPERS,BONNIE	BONNIE PEPPERS
6	MONTEL,DWIGHT F	DWIGHT MONTEL
7	HARVER,JORDAN	JORDAN HARVER
Sheet4

Cell Formulas
Range	Formula
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))

 

[Stuepef]

I like that functionality within excel, but a formula is the method I would like to use to solve this particular instance.

 

[Nishant94]

Or,

Book1
	A	B
1	Name	First Name Last Name
2	WILLIS,GEORGE A	GEORGE WILLIS
3	GREEN,RICK	RICK GREEN
4	THOMAS-MATTS,CHARLOTTE R	CHARLOTTE THOMAS-MATTS
5	PEPPERS,BONNIE	BONNIE PEPPERS
6	MONTEL,DWIGHT F	DWIGHT MONTEL
7	HARVER,JORDAN	JORDAN HARVER
Sheet4

Cell Formulas
Range	Formula
B2	=MID(A2,FIND(",",A2)+1,FIND(" ",A2&" ")-FIND(",",A2)-1)&" "&LEFT(A2,FIND(",",A2)-1)

 

[Stuepef]

Both formulas work great, thank you!