Formula to rid a middle initial when necessary??

[Exc3ln00b]

Noob:



I need to populate a list based on a list of names. The list of names I get is formatted by our HR department.



first name middle initial last name

Ex: John L. smith.



First name and middle initial are in one column and last name is in its own column.



I need to take that data and copy it into one column as first name, one column as last name. And complete remove the middle initial. Not all employees on this list have a middle initial.

Ex:



John Smith.



What’s a formula I can use to automate this?

 

[AlanY]

how about?

Book1
	A	B	C	D	E
1	John L.	Smith		John	Smith
2	John L. S.	Smith		John	Smith
3	John	Smith		John	Smith
Sheet1

Cell Formulas
Range	Formula
D1:D3	D1	=LEFT(A1,IFERROR((FIND(" ",A1)-1),LEN(A1)))
E1:E3	E1	=B1

 

[Exc3ln00b]

Hmm. What if I wanted to merge them all into one column

John L Smith to “John Smith”

 

[AlanY]

Hmm. What if I wanted to merge them all into one column

John L Smith to “John Smith”

you can try

=LEFT(A1,IFERROR((FIND(" ",A1)-1),LEN(A1)))&" "&B1

 

[Fluff]

Another option

=LEFT(A2,FIND(" ",A2&" ")-1)&" "&B2