Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Sorting
I was making a list of movies and I'm wanting to sort them by name, but how
can I get excel to ignore words like "the" and "a" and recognize the first word after that |
#2
|
|||
|
|||
i've done something similar and my solution was to use the following:-
Sub SortThis() Do Until IsEmpty(ActiveCell) strTitle = ActiveCell.Value numSize = Len(strTitle) If Left(strTitle, 3) = "The" Then ActiveCell.Offset(0, 3) = Right(strTitle, (numSize - 4)) & ",The" ElseIf Left(strTitle, 2) = "A " Then ActiveCell.Offset(0, 3) = Right(strTitle, (numSize - 2)) & ", A" Else ActiveCell.Offset(0, 3) = strTitle End If ActiveCell.Offset(1, 0).Select Loop End Sub So, with the first title selected, the macro copies the title to a new column and tags "The" or "A" to the end of the title. Then you can sort the resulting range as you wish (add it into the macro perhaps) "Ronnie" wrote: I was making a list of movies and I'm wanting to sort them by name, but how can I get excel to ignore words like "the" and "a" and recognize the first word after that |
#3
|
|||
|
|||
Ronnie,
My brand new Excel add-in "Special Sort" (beta2) will do that. It ignores leading "A", "An", "The" and sorts the list for you. It also provides six additional "special" methods to sort with. Available -free- upon direct request. Remove XXX from my email address. Jim Cone San Francisco, USA XX "Ronnie" <Ronnie @discussions.microsoft.com wrote in message ... I was making a list of movies and I'm wanting to sort them by name, but how can I get excel to ignore words like "the" and "a" and recognize the first word after that |
#4
|
|||
|
|||
Hi Ronnie,
Another way just using worksheet formulas: SUBSTITUTE is case sensitive Do you include "The" in the sort if it is the first word? Create a helper column. =TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(LOWER(A16)," and "," ")," the "," ")," a "," ")," on "," ")," in "," ")) The Wind in the Willows the wind willows The Cat and the Fiddle the cat fiddle Thousand and One Space Odessey thousand one space odessey The Prince and the Pauper the prince pauper If you want the as the first word to disappear instead of TRIM(A16) use =" " & A16 and then you could add one more substitution --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Ronnie" <Ronnie @discussions.microsoft.com wrote in message ... I was making a list of movies and I'm wanting to sort them by name, but how can I get excel to ignore words like "the" and "a" and recognize the first word after that |
#5
|
|||
|
|||
It is all very well relying on doing things automatically with, say, macros.
The problem with this approach is that unless the macro is well written and positively tested to cover all conditions you can get into the "it must be OK if it was done by the computer" mentality and have a database with some 'funnies'. Either testing to make sure the macro is comprehensive or checking after running you may have to check every record. I would suggest a helper column with the first 5 significant characters of the title. Sorting on this column should do the job. Regards. Bill Ridgeway Computer Solutions "Ronnie" <Ronnie @discussions.microsoft.com wrote in message ... I was making a list of movies and I'm wanting to sort them by name, but how can I get excel to ignore words like "the" and "a" and recognize the first word after that |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maintaining cell reference after sorting | Excel Discussion (Misc queries) | |||
Sorting 1, 1A, 2, 2A, 3, 4, 4A, 4B . . . | Excel Discussion (Misc queries) | |||
sorting number in ascending order | Excel Discussion (Misc queries) | |||
SORTING question | New Users to Excel | |||
Adding a KeyID column for sorting | New Users to Excel |