Sorting
Does Excel 2007 have a built in way to sort a list of names and ignore "The"
in the title? -- Tomk |
Sorting
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. Tomk wrote: Does Excel 2007 have a built in way to sort a list of names and ignore "The" in the title? -- Tomk -- Dave Peterson |
Sorting
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 DataSort 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 ------ Convert your Excel spreadsheet into an online calculator. http://www.spreadsheetconverter.com -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200902/1 |
Sorting
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/ On 31 jan, 18:34, Tomk wrote: Does Excel 2007 have a built in way to sort a list of names and ignore "The" in the title? -- Tomk |
All times are GMT +1. The time now is 07:46 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com