Sorting Full Name Cells By Last Name

DokHoliday

New Member
Joined
Apr 7, 2015
Messages
6
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]
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
This will handle full names with no more than 3 spaces.
Excel Workbook
CDEF
12Rick Paulson*RickPaulson
13James A Perry*James APerry
FirstLastName
 
Upvote 0
This will handle full names with no more than 3 spaces.
FirstLastName

*CDEF
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
CellFormula
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,402
Messages
6,171,915
Members
452,432
Latest member
TiffanyMcllwain

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