sorting by name

[dannyboy8]

if I have names formatted by Mr and Mrs John Doe all in the same column, is
thee a way to sort by last name in excel 2007? the only sort I can figure out
is alpha by 1st letter, so it begins sorting the list by the 1st letter of
the 1st names. Thanks!

 

[Mike H]

Hi,

You need a helper column to extract the last name with this formula

=MID(SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))),FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))+1,256)

having extracted the last names in the new column sort your data on this
last name.

Mike

 

[dannyboy8]

Thanks Mike, strangely, it works on some cells and not others, wonder why
that would be?

 

[Mike H]

Hi,

Doesn't work on some cells isn't a very helpful description of the
problem.The only reason I can think of is may you have a Tilde (~) character
in some cells in which case it won't work. You can change the tilde in the
formula to (say) the @ sign or another more commonly used is the caret ^. I
Forgot it will also fail if there are no spaces in the cell.

Mike

 

[Ragdyer]

With names in Column A, starting in A1, enter this formula in B1:

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))

Copy down as needed, THEN,
while the cells in Column B are *still* selected,
Right click in the selection and choose "Copy",
Right click again and choose "Paste Special",
and click on "Values", then <OK>.

What you just did was remove the formulas and left just the data behind.

Now, select both columns and sort on Column B.

You can delete Column B when finished.

 

[Ragdyer]

Try this formula instead, in case you have trailing spaces:

=TRIM(RIGHT(SUBSTITUTE(TRIM(A17)," ",REPT(" ",99)),99))

 

[Shane Devenshire]

Hi,

This is bad database design. A field containing the prefix and the first
and last name should be broken into three or more fields. If later you need
to use the combination you can bring them back together easily with &, the
concatenation operator.

To break this field into three columns select all the names and choose Data,
Text to Columns, pick Delimited, click Next, choose Space, click Next, pick a
destination cell where you have available room for your three columns and
click Finish.

Now sort on Last Name, First Name or whatever.

Alternatively, if you know that all names have a prefix and a first and last
name then you could use

=MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,20)

to extract the last names to another column and sort on that column.