Sorting

T

Tomk

Does Excel 2007 have a built in way to sort a list of names and ignore "The"
in the title?
 
D

Dave Peterson

Nothing built into excel allows this.

You could use a formula in another column to ignore the first word (like The,
An, A, ...) and then sort by that helper column.
 
C

Chris Bode via OfficeKB.com

Please follow following steps
Suppose that the names are in column A, Now in column B, apply the following
formula to extract the names excluding ‘the’ as
=MID(A1,FIND("The",A1)+3,LEN(A1))
Then
1.Select column B
2.Click Data>Sort from the toolbar
3.In the sort dialog box, select Column B from sort by drop down list
4.Click OK

You will get it!

Have a nice time….


Chris
 
J

JB

With VBA:

Sub Sort()
Set Start = Range("a1")
Start.Offset(0, 1).EntireColumn.Insert Shift:=xlToRight
For Each c In Range(Start, Start.End(xlDown))
If UCase(Left(c, 3)) = "THE" Then
c.Offset(0, 1) = Mid(c, 5)
Else
c.Offset(0, 1) = c
End If
Next c
Start.CurrentRegion.Sort Key1:=Start.Offset(0, 1),
Order1:=xlAscending, Header:=xlGuess
Start.Offset(0, 1).EntireColumn.Delete
End Sub

http://cjoint.com/?cdrExzkMSs

JB
http://boisgontierjacques.free.fr/
 
Top