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]
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,846
Office Version
2010
Platform
Windows
This will handle full names with no more than 3 spaces.
Excel Workbook
CDEF
12Rick Paulson*
Rick
Paulson
13James A Perry*James APerry
FirstLastName
 

DokHoliday

New Member
Joined
Apr 7, 2015
Messages
6
This will handle full names with no more than 3 spaces.
FirstLastName

*CDEF
12Rick Paulson*RickPaulson
13James A Perry*James APerry

<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>
</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.
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,846
Office Version
2010
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,491
Messages
5,468,925
Members
406,620
Latest member
Gitani123

This Week's Hot Topics

Top