How to sort a list with Mr, Mrs, Miss ?

[Martin ©¿©¬]

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

 

[Bob Umlas]

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

 

[JB]

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

 

[Shane Devenshire]

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.

 

[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

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

 

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

Thanks Shane
I'll try that too