Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

sort by last name when first + last are in the same cell

0

Hi everyone

I have a list of musicians and artists and I am trying to sort by their last name unfortunately the list that I have has both their first and last name in the same cell in column A. Is there any easy way to solve this problem?

thanks for any help!

Answer
Discuss

Answers

1
Selected Answer

There is no direct solution for your problem. Therefore create a helper column next to the one with the mixed names and enter the following formula (in row 2, and presuming that the names are in column A).

=IFERROR(RIGHT($A2,LEN($A2)-FIND("*",SUBSTITUTE($A2," ","*",LEN($A2)-LEN(SUBSTITUTE($A2," ",""))))),$A2)

The formula will extract the last word from column A. In most cases that will be the last name. Copy down, sort on the helper column, then delete it.

Discuss

Discussion

Thanks you soooo much!!! I don't understand the formula at all haha but it works like a charm :) I would upvote you if I could but don't have the permission for it apparently.
music_man (rep: 2) Aug 2, '17 at 11:33 am
I'm glad I could help!
The trick is in LEN(A1)-LEN(SUBSTITUTE(A1," ",""). This returns the number of blank spaces in the name, call it "n". Then all the formula does is to find the location of the nth space and return the text to the right of that location. The process will return an error if there is no space, The IFERROR part ensures that the whole name is returned instead of an error.
Variatus (rep: 4889) Aug 2, '17 at 8:37 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login