Home |
Search |
Today's Posts |
#1
|
|||
|
|||
alphabetical list
ok...I have complied a list of my DVD collection and what I am trying to do
is alphabatize the list. What problem I have when doing it is when I click sort button it always groups words like "A" and "The". I want it to ignore those words and focus on the word after "A" or "The" |
#2
|
|||
|
|||
Create a helper column and add this formula to it
=SUBSTITUTE(SUBSTITUTE(A20,"The ",""),"A ","") Then sort both columns by the helper column -- HTH Bob Phillips "Ronnie" wrote in message ... ok...I have complied a list of my DVD collection and what I am trying to do is alphabatize the list. What problem I have when doing it is when I click sort button it always groups words like "A" and "The". I want it to ignore those words and focus on the word after "A" or "The" |
#3
|
|||
|
|||
You need a helper column by which to sort.
=left(a1,5) will give you the 5 leftmost characters Sort on the helper column (to bring together cells that will need to be changed) and amend any unwanted characters to give 5 significant characters As a refinement you could highlight the helper column and click on <Edit<Copy<Edit<Paste special<Values<OK to change the output of all formulas to text. Now use this column to sort. Regards. Bill Ridgeway Computer Solutions "Ronnie" wrote in message ... ok...I have complied a list of my DVD collection and what I am trying to do is alphabatize the list. What problem I have when doing it is when I click sort button it always groups words like "A" and "The". I want it to ignore those words and focus on the word after "A" or "The" |
#4
|
|||
|
|||
Actually you don't want to pick off a trailing A on a word, so make that
=SUBSTITUTE(SUBSTITUTE(" " & A20 & " ", " The "," ")," A "," ") note the space inserted before and after the cell to be tested and the space before and after the words that are to be removed and the single space for the replacement. Accept the spaces remaining when finished because trimming would add another nesting level. When you determine all of the words you want to remove and depending on their location and removal of punctuation you might be looking at a user defined function since you can only go to seven nesting levels in a worksheet formula. But the original question may have only be removal of a leading The and a Leading A. =SUBSTITUTE(SUBSTITUTE("^" & A20 & "^", "^The "," "),"^A ","^") again only used for sorting so it doesn't matter what it looks like. Might be more suitable for the use of Regular Expressions. --- 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 "Bob Phillips" wrote in message ... Create a helper column and add this formula to it =SUBSTITUTE(SUBSTITUTE(A20,"The ",""),"A ","") Then sort both columns by the helper column -- HTH Bob Phillips "Ronnie" wrote in message ... ok...I have complied a list of my DVD collection and what I am trying to do is alphabatize the list. What problem I have when doing it is when I click sort button it always groups words like "A" and "The". I want it to ignore those words and focus on the word after "A" or "The" |
#5
|
|||
|
|||
I'm new with excell.......so could you be REALLY specific
"Bill Ridgeway" wrote: You need a helper column by which to sort. =left(a1,5) will give you the 5 leftmost characters Sort on the helper column (to bring together cells that will need to be changed) and amend any unwanted characters to give 5 significant characters As a refinement you could highlight the helper column and click on <Edit<Copy<Edit<Paste special<Values<OK to change the output of all formulas to text. Now use this column to sort. Regards. Bill Ridgeway Computer Solutions "Ronnie" wrote in message ... ok...I have complied a list of my DVD collection and what I am trying to do is alphabatize the list. What problem I have when doing it is when I click sort button it always groups words like "A" and "The". I want it to ignore those words and focus on the word after "A" or "The" |
#6
|
|||
|
|||
I'm new with excell....could you be REALLY specific
"David McRitchie" wrote: Actually you don't want to pick off a trailing A on a word, so make that =SUBSTITUTE(SUBSTITUTE(" " & A20 & " ", " The "," ")," A "," ") note the space inserted before and after the cell to be tested and the space before and after the words that are to be removed and the single space for the replacement. Accept the spaces remaining when finished because trimming would add another nesting level. When you determine all of the words you want to remove and depending on their location and removal of punctuation you might be looking at a user defined function since you can only go to seven nesting levels in a worksheet formula. But the original question may have only be removal of a leading The and a Leading A. =SUBSTITUTE(SUBSTITUTE("^" & A20 & "^", "^The "," "),"^A ","^") again only used for sorting so it doesn't matter what it looks like. Might be more suitable for the use of Regular Expressions. --- 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 "Bob Phillips" wrote in message ... Create a helper column and add this formula to it =SUBSTITUTE(SUBSTITUTE(A20,"The ",""),"A ","") Then sort both columns by the helper column -- HTH Bob Phillips "Ronnie" wrote in message ... ok...I have complied a list of my DVD collection and what I am trying to do is alphabatize the list. What problem I have when doing it is when I click sort button it always groups words like "A" and "The". I want it to ignore those words and focus on the word after "A" or "The" |
#7
|
|||
|
|||
I'm new with excell....could you be REALLY specific
"Bob Phillips" wrote: Create a helper column and add this formula to it =SUBSTITUTE(SUBSTITUTE(A20,"The ",""),"A ","") Then sort both columns by the helper column -- HTH Bob Phillips "Ronnie" wrote in message ... ok...I have complied a list of my DVD collection and what I am trying to do is alphabatize the list. What problem I have when doing it is when I click sort button it always groups words like "A" and "The". I want it to ignore those words and focus on the word after "A" or "The" |
#8
|
|||
|
|||
Go to the first free cell to the right of data in the spreadsheet
Type - =left(a1,5) Copy the formula to all cells in the column Sort on that column Regards. Bill Ridgeway Computer Solutions "Ronnie" wrote in message ... I'm new with excell.......so could you be REALLY specific "Bill Ridgeway" wrote: You need a helper column by which to sort. =left(a1,5) will give you the 5 leftmost characters Sort on the helper column (to bring together cells that will need to be changed) and amend any unwanted characters to give 5 significant characters As a refinement you could highlight the helper column and click on <Edit<Copy<Edit<Paste special<Values<OK to change the output of all formulas to text. Now use this column to sort. Regards. Bill Ridgeway Computer Solutions "Ronnie" wrote in message ... ok...I have complied a list of my DVD collection and what I am trying to do is alphabatize the list. What problem I have when doing it is when I click sort button it always groups words like "A" and "The". I want it to ignore those words and focus on the word after "A" or "The" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i do an alphabetical list of songs for a booklet | Excel Discussion (Misc queries) | |||
Alphabetical list of Numbers are not correct! | Excel Discussion (Misc queries) | |||
Extracting Values on one list and not another | Excel Discussion (Misc queries) | |||
Make Data validation List Alphabetical?? | Excel Worksheet Functions | |||
creating pages from an alphabetical list | Excel Worksheet Functions |