Sort by Last Name when name is combined

[Lisapbs]

I inherited a worksheet where first and last name are combined in one cell
(i.e. Jane Doe) The first name is listed first. Can I sort this this by the
LAST name? How? If not, how can I split the text into two cells FIRST NAME
and LAST NAME?

Thanks! Lisa

 

[AKphidelt]

Hey Lisa,

See if this works. Insert a new column next to the name. Highlight the names
in the list.

Go to Data ----> Text To Columns

Select the Deliminited option
Now check the box that says Space and then
Click finish.

Let me know if this works.

 

[Lisapbs]

This works great for splitting into two names! Thanks. Still wonder if I
can sort by the second name i one column, however.....I have LOTS AND LOTS of
worksheets to use your formula on if not!

 

[AKphidelt]

Yea, I wish I could help you on that... but without splitting up the names
and without using VBA I do not know of a way to sort by last names. Theres
formulas that can put the last name in front of the first name... but then it
would just be easiar to test to columns the names if you were gonna do that.
Let me know.

 

[Pete_UK]

Assuming your names are in column A, starting with A2, with a single
space between them, enter this formula in B2:

=RIGHT(A2,LEN(A2)-FIND(" ",A2))&" "&LEFT(A2,FIND(" ",A2))

this will reverse the names to give you surname first followed by
first name. If you want the names to be separated into different
columns, try this:

B2: =RIGHT(A2,LEN(A2)-FIND(" ",A2))
C2: =LEFT(A2,FIND(" ",A2))

Either way, you can then copy the formulae down for as many entries as
you have in column A.

You can then sort on column B (or B and C in the second case, so as to
distinguish between Smith David and Smith John).

Hope this helps.

Pete

 

[renaissanceme]

I inherited a worksheet where first and last name are combined in one cell
(i.e. Jane Doe) The first name is listed first. Can I sort this this by the
LAST name? How? If not, how can I split the text into two cells FIRST NAME
and LAST NAME?

Thanks! Lisa

Hi Lisa,

I've had this problem before, and I used the approach of splitting the
name into it's first and last names.

if "Jane Doe" is in Cell A1:

in B1: =left(a1,find(" ",A1,1)-1) - this should return "Jane"
in C1: =right(a1,len(a1)-len(b1)) - this should return "Doe"

This won't work for people that have a space in their first name, and
it might give some curious results on people with a middle initial.
So, you will have to review the results, but it should work on 90% of
names, which is a nice headstart.

Dave