Sorting Full Name Cells By Last Name

[DokHoliday]

Hi Team,

I have a list of full names in a row that I need sorted by last name. I know I can separate the first and last names by using Text To Columns and the space as a separator, but I was wondering if there was a clever formula to spit only the last name out into a helper column automatically. At the moment, I'm copying and pasting these names along with several other rows of data onto Sheet 1 of a template, with Sheet 2 manipulating it into the final product. Is there a way to add a formula as part of Sheet 2 so that when I copy and paste the raw data onto Sheet 1, it will spit only the last name into a new row that I can sort separately?

So far what my Googling has came up with is [FONT=&quot]=MID(A2,FIND(" ",A2)+1,100) assuming my list of full names starts at A2, which works great until I run into Full names with middle initials.

Any help would be greatly appreciated!

-Ruben C.[/FONT]

 

[JoeMo]

This will handle full names with no more than 3 spaces.

Excel Workbook
	C	D	E	F
12	Rick Paulson	*	Rick	Paulson
13	James A Perry	*	James A	Perry
FirstLastName

 

[DokHoliday]

This will handle full names with no more than 3 spaces.
FirstLastName

*	C	D	E	F
Rick Paulson	*
James A Perry	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:183px;"><col style="width:12px;"><col style="width:80px;"><col style="width:103px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="bgcolor: #ccffff"]Rick[/TD]
[TD="bgcolor: #ccffff"]Paulson[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="bgcolor: #ccffff"]James A[/TD]
[TD="bgcolor: #ccffff"]Perry[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula E12 =TRIM(CHOOSE(LEN(TRIM(C12))-LEN(SUBSTITUTE(TRIM(C12)," ","")),LEFT(TRIM(C12),SEARCH(" ",TRIM(C12),1)-1),LEFT(TRIM(C12),SEARCH(" ",TRIM(C12),SEARCH(" ",TRIM(C12),1)+1)),LEFT(TRIM(C12),SEARCH(" ",TRIM(C12),SEARCH(" ",TRIM(C12),SEARCH(" ",TRIM(C12),1)+1)+1)))) F12 =TRIM(CHOOSE(LEN(TRIM(C12))-LEN(SUBSTITUTE(TRIM(C12)," ","")),RIGHT(TRIM(C12),LEN(TRIM(C12))-SEARCH(" ",TRIM(C12))),RIGHT(TRIM(C12),LEN(TRIM(C12))-SEARCH(" ",TRIM(C12),SEARCH(" ",TRIM(C12),1)+1)),RIGHT(TRIM(C12),LEN(TRIM(C12))-SEARCH(" ",TRIM(C12),SEARCH(" ",TRIM(C12),SEARCH(" ",TRIM(C12),1)+1)+1)))) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

You are a freaking wizard! It totally works, thank you so much. I'm going to try and break down your formulas piece by piece on my own in an effort to understand the magic within, but could you briefly walk me through what your thought process for this was? If it's above my pay grade or if you have to go put out other fires, I definitely understand and once again, million thanks!

-Ruben C.

 

[JoeMo]

You are a freaking wizard! It totally works, thank you so much. I'm going to try and break down your formulas piece by piece on my own in an effort to understand the magic within, but could you briefly walk me through what your thought process for this was? If it's above my pay grade or if you have to go put out other fires, I definitely understand and once again, million thanks!

-Ruben C.

You are welcome.

Essentially, the CHOOSE function allows choosing the proper formula to apply based on the number of spaces encountered in the name. The number of spaces is the difference between the length of the full name and the length of the full name with the spaces removed.