Hi
I have a list of names in a spreadsheet that includes Titles, Names &
addresses

Name Address Town Postcode
Mr A Burns 24 Hill street town postcode
Mrs B Mitchel 45 Acreback road town postcode
Miss G Hay The sheddings town postcode
etc etc etc etc

How do I sort this list by surname?
It keeps sorting by Miss, Mr, Mrs
--
Martin
©¿©¬

If it's consistently title, initial, surname, or title, first name, surname,
then you can select the column, use Data/Text-to-columns using a delimiter
of space and when done sort on the 3rd column.
Bob Umlas
Excel MVP

<Martin ©¿©¬ @nohere.net wrote in message
...
Hi
I have a list of names in a spreadsheet that includes Titles, Names &
addresses

Name Address Town Postcode
Mr A Burns 24 Hill street town postcode
Mrs B Mitchel 45 Acreback road town postcode
Miss G Hay The sheddings town postcode
etc etc etc etc

How do I sort this list by surname?
It keeps sorting by Miss, Mr, Mrs
--
Martin
©¿©¬

With VBA:

http://cjoint.com/?dDtICJNMLz

Sub Sort()
Set First = Range("a1")
ncol = First.CurrentRegion.Columns.Count
nlig = First.CurrentRegion.Rows.Count - 1
First.Offset(0, ncol).EntireColumn.Insert Shift:=xlToRight
For Each c In First.Offset(1, 0).Resize(nlig, 1)
c.Offset(0, ncol) = WithOutCivil(c.Value)
Next c
First.CurrentRegion.Sort Key1:=First.Offset(1, ncol),
Order1:=xlAscending, Header:=xlGuess
First.Offset(0, ncol).EntireColumn.Delete
End Sub

Function WithOutCivil(chaine)
civilite = Array("Mr", "Miss", "Mrs")
WithOutCivil = chaine
p = InStr(chaine, " ")
If p < 0 Then
If Not IsError(Application.Match(UCase(Left(chaine, p - 1)),
civilite, 0)) Then
WithOutCivil = Mid(chaine, p + 1)
End If
End If
End Function

JB
http://boisgontierjacques.free.fr



On 27 mar, 17:48, Martin ©¿©¬ @nohere.net wrote:
Hi
I have a list of names in a spreadsheet that includes Titles, Names &
addresses

Name * * * * * *Address * * * * Town * *Postcode
Mr A Burns * * *24 Hill street * * * * *town * *postcode
Mrs B Mitchel * 45 Acreback road * * * *town * *postcode
Miss G Hay * * *The sheddings * * * * * town * *postcode
etc * * * * * * etc * * * * * * * * * * etc * * etc

How do I sort this list by surname?
It keeps sorting by Miss, Mr, Mrs
--
Martin
©¿©¬

Hi,

And for a third approach you can use a formula. Assuming your first name
starts in A2 then enter the following formula in an adjactent column and sort
on this new column:

=MID(A2,FIND(" ",A2,FIND(" ",A2)+1)+1,9)

If the surnames are longer than 9 characters just increase the last argument.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Martin ©¿©¬ @nohere.net" wrote:

Hi
I have a list of names in a spreadsheet that includes Titles, Names &
addresses

Name Address Town Postcode
Mr A Burns 24 Hill street town postcode
Mrs B Mitchel 45 Acreback road town postcode
Miss G Hay The sheddings town postcode
etc etc etc etc

How do I sort this list by surname?
It keeps sorting by Miss, Mr, Mrs
--
Martin
©¿©¬

On Fri, 27 Mar 2009 13:05:14 -0400, "Bob Umlas"
wrote:

If it's consistently title, initial, surname, or title, first name, surname,
then you can select the column, use Data/Text-to-columns using a delimiter
of space and when done sort on the 3rd column.
Bob Umlas
Excel MVP

Thanks Bob
that worked well & I was able to make address labels too
--
Martin
©¿©¬

<Martin ©¿©¬ @nohere.net wrote in message
.. .
Hi
I have a list of names in a spreadsheet that includes Titles, Names &
addresses

Name Address Town Postcode
Mr A Burns 24 Hill street town postcode
Mrs B Mitchel 45 Acreback road town postcode
Miss G Hay The sheddings town postcode
etc etc etc etc

How do I sort this list by surname?
It keeps sorting by Miss, Mr, Mrs
--
Martin
©¿©¬

On Fri, 27 Mar 2009 16:05:01 -0700, Shane Devenshire
wrote:

Hi,

And for a third approach you can use a formula. Assuming your first name
starts in A2 then enter the following formula in an adjactent column and sort
on this new column:

=MID(A2,FIND(" ",A2,FIND(" ",A2)+1)+1,9)

If the surnames are longer than 9 characters just increase the last argument.

Thanks Shane
I'll try that too
--
Martin
©¿©¬