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

  • Thread starter Thread starter Martin ©¿©¬
  • Start date Start date
M

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
 
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
 
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
 
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 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
Click to expand...

Thanks Bob
that worked well & I was able to make address labels too
--
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.
Click to expand...

Thanks Shane
I'll try that too
 
Back
Top