Parsing out First and Last name from Excel field

Question

I have a field (column) in excel in the format of "LastName, FirstName MiddleInitial" with a space between the comma after the last name and the first name and a second space between the middle initial and the first name (no comma after the first name). Is there a way to identify which cells have a middle initial on the right hand side and then eliminate the middle initial for all cells such that the output will look like "LastName, FirstName"?

Thanks!

Answer 1

What you want to do is to be able to parse the field into multiple fields, and then rejoin then with a simple excel formula. To take a field and put it into multiple columns you can use the following approach for "Text to Columns"

Text to Columns

Once you have 3 columns of text, you can group the first two together by using a formula like =A1&", "&B1 in the column you want to have the value. (In my example, the last name would be in A1, and first name in B1)

Answer 2

You could also do it as a cell formula without changing your original data. I built this up using the models presented on How to split a string based on “:” in MS-Excel?, over at SuperUser. Assuming you have a name (Public, John Q) in a1:

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

It looks very complicated, but really isn't:

Using these two components:

• Get everything to the left of the first space in a string: LEFT(A1,FIND(" ",A1))
• Get everything to the right of the first space in a string: MID(A1,FIND(" ",A1)+1,LEN(A1)))

We can grab these pieces:

• Get the last name: LEFT(A1,FIND(",",A1)+1)

• Get the first name and middle name: MID(A1,FIND(" ",A1)+1,LEN(A1)))

Then recurse by putting the "left of a space" construction around the "right of a string" construction:

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

Unless there is no second space (when there's no middle name.) That's what the Iferror is for - in that case, you want the entire length of what you got after the first space:

• LEFT(MID(A1,FIND(" ",A1)+1,LEN(A1)),IFERROR(FIND(" ",MID(A1,FIND(" ",A1)+1,LEN(A1)))-1,LEN(MID(A1,FIND(" ",A1)+1,LEN(A1)))))

Put them together for lastname, firstname:

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