Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I alphabetize data excluding words like "a" and "the"?
I'm cataloging a list of movies, and I'd like to be able to alphabetize the
titles ignoring words like "a" and "the." Is this possible? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I alphabetize data excluding words like "a" and "the"?
Create a helper column and strip leading The and A and An,
=Substitute(Substitute(Substitute(A1,"The ",""),"A ",""),"An","") -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Faith" wrote in message ... I'm cataloging a list of movies, and I'd like to be able to alphabetize the titles ignoring words like "a" and "the." Is this possible? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I alphabetize data excluding words like "a" and "the"?
Here's one option that might work for you. Let's say your titles are in
column A. Insert a new column B and enter this formula: =IF(LEFT(A1,2)="a ",MID(A1,3,LEN(A1)),IF(LEFT(A1,4)="the ",MID(A1,5,LEN(A1)),A1)) Copy the formula down through column B as needed. Then, when you sort, sort by column B. You can also hide column B if you want. HTH, Elkar "Faith" wrote: I'm cataloging a list of movies, and I'd like to be able to alphabetize the titles ignoring words like "a" and "the." Is this possible? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I alphabetize data excluding words like "a" and "the"?
This is clever.
-- Brevity is the soul of wit. "Elkar" wrote: Here's one option that might work for you. Let's say your titles are in column A. Insert a new column B and enter this formula: =IF(LEFT(A1,2)="a ",MID(A1,3,LEN(A1)),IF(LEFT(A1,4)="the ",MID(A1,5,LEN(A1)),A1)) Copy the formula down through column B as needed. Then, when you sort, sort by column B. You can also hide column B if you want. HTH, Elkar "Faith" wrote: I'm cataloging a list of movies, and I'd like to be able to alphabetize the titles ignoring words like "a" and "the." Is this possible? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I alphabetize data excluding words like "a" and "the"?
Hello, Faith!
You wrote on Mon, 13 Nov 2006 13:28:01 -0800: F I'm cataloging a list of movies, and I'd like to be able to F alphabetize the titles ignoring words like "a" and "the." Is F this possible? You probably could do this with VBA but, for a one off sort, you could also make a helper column with "the " and "a " replaced by "" and sort on that. It depends, I guess, whether you intend to do it frequently. James Silverton Potomac, Maryland E-mail, with obvious alterations: not.jim.silverton.at.comcast.not |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I alphabetize data excluding words like "a" and "the"?
That works, but if the first word of a movie without A, or AN or THE, starts
with any of those characters, this formula removes those first 1 - 3 letters. IE: "Angels Among Us" in column A, becomes "gels Among Us" in the helper column. "Bob Phillips" wrote: Create a helper column and strip leading The and A and An, =Substitute(Substitute(Substitute(A1,"The ",""),"A ",""),"An","") -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Faith" wrote in message ... I'm cataloging a list of movies, and I'd like to be able to alphabetize the titles ignoring words like "a" and "the." Is this possible? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I alphabetize data excluding words like "a" and "the"?
No it doesn't, notice that there are spaces included
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"The ",""),"A ",""),"An ","") So Angels will not be affected, of course if the movie's name is "Together On A Sunday" and you are using a capital A before Sunday that will be stripped but if that's the case one can add some more to it =IF(LEFT(A1,2)="A ",SUBSTITUTE(A1,"A ",""),IF(LEFT(A1,3)="An ",SUBSTITUTE(A1,"An ",""),IF(LEFT(A1,4)="The ",SUBSTITUTE(A1,"The ","",1),A1))) -- Regards, Peo Sjoblom "val8rie" wrote in message ... That works, but if the first word of a movie without A, or AN or THE, starts with any of those characters, this formula removes those first 1 - 3 letters. IE: "Angels Among Us" in column A, becomes "gels Among Us" in the helper column. "Bob Phillips" wrote: Create a helper column and strip leading The and A and An, =Substitute(Substitute(Substitute(A1,"The ",""),"A ",""),"An","") -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Faith" wrote in message ... I'm cataloging a list of movies, and I'd like to be able to alphabetize the titles ignoring words like "a" and "the." Is this possible? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting excluding "the" | Excel Discussion (Misc queries) | |||
Sorting text that begins with "the" or "a" | Excel Discussion (Misc queries) |