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"
0
Ronnie (39)
7/26/2005 9:04:01 AM
excel.newusers 15348 articles. 2 followers. Follow

7 Replies
344 Views

Similar Articles

[PageSpeed] 5

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" <Ronnie@discussions.microsoft.com> wrote in message
news:AB4E0F33-4472-47CE-BC38-273594D5451D@microsoft.com...
> 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"


0
phillips1 (803)
7/26/2005 10:05:26 AM
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" <Ronnie@discussions.microsoft.com> wrote in message 
news:AB4E0F33-4472-47CE-BC38-273594D5451D@microsoft.com...
> 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" 


0
Bill
7/26/2005 9:29:39 PM
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" <phillips@tiscali.co.uk> wrote in message news:uocgemckFHA.3164@TK2MSFTNGP15.phx.gbl...
> 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" <Ronnie@discussions.microsoft.com> wrote in message
> news:AB4E0F33-4472-47CE-BC38-273594D5451D@microsoft.com...
> > 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"
>
>


0
7/28/2005 5:35:37 AM
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" <Ronnie@discussions.microsoft.com> wrote in message 
> news:AB4E0F33-4472-47CE-BC38-273594D5451D@microsoft.com...
> > 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" 
> 
> 
> 
0
Ronnie (39)
7/28/2005 6:46:30 PM
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" <phillips@tiscali.co.uk> wrote in message news:uocgemckFHA.3164@TK2MSFTNGP15.phx.gbl...
> > 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" <Ronnie@discussions.microsoft.com> wrote in message
> > news:AB4E0F33-4472-47CE-BC38-273594D5451D@microsoft.com...
> > > 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"
> >
> >
> 
> 
> 
0
Ronnie (39)
7/28/2005 6:49:04 PM
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" <Ronnie@discussions.microsoft.com> wrote in message
> news:AB4E0F33-4472-47CE-BC38-273594D5451D@microsoft.com...
> > 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"
> 
> 
> 
0
Ronnie (39)
7/28/2005 6:49:06 PM
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" <Ronnie@discussions.microsoft.com> wrote in message 
news:5775F608-BEA9-43B7-8A7C-9E6CC31B53DF@microsoft.com...
> 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" <Ronnie@discussions.microsoft.com> wrote in message
>> news:AB4E0F33-4472-47CE-BC38-273594D5451D@microsoft.com...
>> > 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"
>>
>>
>> 


0
Bill
7/28/2005 9:41:19 PM
Reply:

Similar Artilces:

Import Lists?
Is there a way to copy or import members (list of email addresses) from a Global Address List into a Excel spreadsheet? I've been able to put into a Word doc but it's not the results I'm looking for. Thanks!! You can use LDIFDE to run the query, you can then paste the results into an Excel spreadsheet. <anonymous@discussions.microsoft.com> wrote in message news:490c01c49feb$cf922950$a401280a@phx.gbl... > Is there a way to copy or import members (list of email > addresses) from a Global Address List into a Excel > spreadsheet? I've been able to put into a Wo...

Where can I find a list of known CRM 1.2 issues and workarounds?
I am running a test of CRM 1.2 from MSDN Universal and I am having a number of minor strange issues. Is there a list of known problems, and workarounds posted anywhere? Have any patches been issued or other technical update articles? Is there some sort of monitored support newsgroup or something I should purchase to be able to more effectively evaluate the product? Thanks, Bill Walter This is the only public newsgroup on CRM apart from foreign language versions. For support you would need an agreement with Microsoft MBS. Feel free to ask any technical questions here as we normally can get...

Can Distribution List Sent Show Only One Recipient in Emails Recei
This is a re-write of a previous post, that didn’t get resolved. I’m restating it here to get a fresh start / better response. Also, please note that I’m not very technically savvy, so if there is a solve for this, please advise me in very plain language. Thanks in advance. Scenario: I'm a new user of Outlook 2007. Say I have a large group of clients. I want to create a boilerplate email for, that goes out to all the recipients. However, I want each email sent to be received by a single client, and at the same time not have that client be aware of the other clients (It...

Problem on Outlook 2000 distribution list
I create a distribution list long time ago. It had 10 people on it. Last time I modify, add or delete people on distribution list, and I use this to send out. I found that the email only send to 3 people. It should be send to 10 different people. Please tell me why it will happen and how to solve it. Thanks a lot, Amon ...

Comparing first and last names in two lists #2
I have two data sources that each contain about 8000 names in seperate fields for first and last name. I can put them on seperate worksheets or append one to the other. I need to make them match and find out where they don't. Messed with consolidate but not sure if that will work for more than one column at a time. FYI data set one is from a school transportation database and data set two is from the main student database from the same school district. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages dir...

Cannot see receipients in global list while using OWA
I have exchange 2003 with outlook 2003 as client. Whenever I use OWA and try to create new email and click on TO to select receiver's name from the global list, I get a differnent screen which as me to enter name to search. I want to display names so I can select from the list. Here is the screen that I get each time I click on TO. I should be seeing everyone's name so I can select. If I connect locally, I do get the list and there is no problem. This happens only with OWA. Thanks -------------------------------------------------------------------------------- OWA d...

Downloading in Outlook 2003 creates a file "needs list" instead of d/l file
When I have an email with attachment, and I go to download it, it downloads it and sometimes renames it to "needs list". It is the same size as the original file, however it cant be opened by any known program. In this case, it should be a .dwg autocad file, but it has happened a couple of times. the name in the saveas box is correct, you download it, and go to that directory and what you were expecting to be there isnt, instead needs list is there. Anyone here of this before? I searched Google, and the newsgroups and the MShelp files, and found nothing. Any ideas woul...

Large List Sorting
Hello, All! Have a problem. I'm developing an application which reading a large database file (DBF) and displaying it in the list control. Because of the size of the file (>15 MB, later it will be increased) I'm using a virtual list (owner data). But now I need to sort data in the list. AFAIK to perfirm this I should read the whole file. In this case my program starts several minutes!!! It doesn't have to be like this [(C) Pink Floyd]... Is there any methods to load a large amount of data as quickly as possible? Or I just should humble, 'cause sorting of large amount of d...

Distribution Lists not replicating
We are preparing to migrate from exchange 5.5 to 2003. I am using the exdeploy tools and following all the steps. For some reason the only thing that is failing to replicate is the distribtion lists. When I open System Manager and look at the 5.5 mailboxes I don't see any of the distribtion lists...so there's no way I can move them to the 2003 server. Any ideas? Have you looked at this: http://www.msexchange.org/tutorials/Site-Consolidation-Tools-Exchange2003.ht ml "KJ" <anonymous@discussions.microsoft.com> wrote in message news:025d01c499d3$c8b8f410$a601280a...

All Address Lists #2
I am trying to Hide the 'All Users' 'All Groups' and 'All Contacts' address lists below the 'All Address Lists' empty container, and provide company specific 'All' address lists instead. Every time I try to hide the non-required address lists, they are still visible to users, and when they try to view the list the error 'This Bookmark Is Not Valid' is presented. Is there a way to either filter the 'All Users etc..' address lists so that one domain can only see thier own domain? or is there a successful way to 'hide' the li...

Insert list of figures does not work for Pictures
Version: 2004 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I can generate a list of all of my inserted objects by caption: Figure, Example, Table, Song, Chart. But when I try to generate a list of the pictures I have inserted (caption reads Picture ...), I get the following message: Error! No table of figures entries found. It's as if the macro that reads the caption list doesn't have Picture in its internal dictionary. I do have the Picture captions in a text box that is formatted as "tight" to allow text-wraparound; could that be causing the problem? If so h...

NEW MENU
Hi, In Excel 2003, one could to create a new menu and then choose commands for such new menu. I used to create a list of files to open (different from the "most recent" list). How does one do it in Excel 2007? Thanks. "CF" <CF@discussions.microsoft.com> wrote in message news:6AF8A7BC-5013-4BEC-B13A-3B7B8F4E4220@microsoft.com... > Hi, > In Excel 2003, one could to create a new menu and then choose commands for > such new menu. I used to create a list of files to open (different from > the > "most recent" list). > &g...

Lost contact list 04-22-10
I amusing Windows live mail...it had no problem picking up my contacts from Outlook Express. Now that I syncronized messages from my Hotmail e-mail account and my Telus email account into Windows Live, my contact list is only the two contacts from the hotmail account. All the rest are gone. They are still in the Address Book, but I cant access them How can I get it back into Windows Live Mail ??? Help please. You signed into the Hotmail account when you started Windows Live Mail. This is optional and not at all related to getting your mail. When you sign in, WLM sync...

Send Emails With A Different Name Than How Its Listed in the GAL
Hi all, I have a quick question: My client is unhappy with how his emails get sent out from MS Outlook. The GAL on our server lists names as [Last Name], [First Name]. This is how the majority of our clients want it. However, he wants his to be [First Name] [Last Name]. So, all recipients getting emails from him will see his name as First Name, Last Name. We can't do this for him on our GAL, since it would obviously cause an inconsistency if we did it only for one name. But, I figured I'd post here to see if anything else can be done to accomodate him. Thanks in advance for your help...

ditribution list
hi all, i need to create a list of contact from an outlook contact to exchange. this because i need to centralize the address contact to global address list on exchange. all of this contact will be sent via outlook with an external isp account. can i create this type of distribution list on exchange or it is more simple to share one outlook contact to other. thanks Giulio Hi, If you create mail enabled contacts in your AD then they will be published to you GAL. That is under the presumtion you do not have any custom GALs, AD issues etc etc. Oliver You can also share address books...

Suppliers with Top Items List
Can anyone let me know how to show suppliers on Top Item List? I badly need this. I tried my way but not successful. -- Many Thanks Install the RMS Toolkit trial download you requested and check off Custom Reports/Templates during installation. Now run Manager, Reports, Custom, Top Items Report. This report includes Suppliers, Dept, Category, and Sub-Descriptions as columns and filter options. There are over 100 free reports and templates included with our RMS Toolkit trial. Visit: www.digitalretailer.com/trial Greg Digital Retail Solutions www.digitalretailer.com/RMSaddins (v1.x and ...

Importing List from Excel to Word printing labels spacing problems
When I import my mailing list from Excel to Word to print mailing labels (30 per page) my labels have large spaces between the lines. For example they look like this: John Doe P.O. Box 000 City, State, Zip And then if I have a business name before John Doe it gets cut off. Is there a way to decrease the space between the lines so it fits better? I have figured out how to do one at a time, but we have 4,000 of these to print! I would like it to look like: John Doe P.O. Box 000 City, State, Zip Thanks for any help! Sounds like a question for a Word newsgroup, not...

What file to look for on my hard drive back up to recover my Entourage contact list?
Version: 2004 Operating System: Mac OS X 10.6 (Snow Leopard) Hi, <br><br>I have a back up of all my hard drive on a back up drive. Good news. <br><br>But... <br><br>I just had a new drive installed and I had to re-install Entourage (too long to explain why), and I my Entourage contacts were not transferred to the new drive. <br><br>I want to import my contact file from my back up drive but I don't know what file to look for and where to look for. <br><br>Any suggestion? Thanks On 2/6/10 6:45 AM, in article 59bb259f.-1@webc...

drop down lists in Business Portal
When requiring a query selection for filtering (such as in Purchase Request), the go button (right arrow on green background) should be automatically pressed when a selection is clicked. I think it would be as simple as adding an 'on changed' event to the drop down list to call the button, and would make the user interface more intuitive and comfortable (less frustration than if you're waiting for your results and not realize you didn't hit the button). ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most vote...

Fill in text from drop down list box in Excel
Hello, I have created a drop down list box but the list is getting too big and the user has to scroll down the list to select the data. Is there a feature I can use to display the data that start with the same string. For example: If I type "Be" in my box, the box could display the list that start with "BE or Be". I created the drop down list by using "Data", chose "Validation", and chose "List" from the "Allow" tab. Thanks for your help. Bexi Bexi If your drop-down list if from Data Validation, you cannot have Autocomplete. T...

hyperlinks from a list? using excel 2000
jacob Okay, I'll get right to the issue. I'm using the List feature of Data Validation in A1, with a range of names on the Z file. The list contains a name and telephone number and email address. What I'm trying to do is insert an email hyperlink in that same cell so when a List item is chosen from the drop down list, you can click on A1 to send an email address. The email hyperlink would need to change along with the name. I don't think this will work, so what about creating a clickable link in A2 that changes with ...

Can I merge a list of email addresses from exel into outlook?
w/out having to enter each on separately, can I merge the email addresses into a group? Hi MDuda See this page http://www.rondebruin.nl/mail/importcontacts.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "MDuda" <MDuda@discussions.microsoft.com> wrote in message news:B58DEF30-7EEA-4244-BBED-C1851156DF6C@microsoft.com... > w/out having to enter each on separately, can I merge the email addresses > into a group? ...

dependent combo box list, with indirect reference
I would like to create a dependent combo box. I have a cell linked to anothe combo box, say C1. I have one list in D1:D4, another list in E1:E3. In C2, I have value "D1:D4", in C3 I have "E1:E3". For the input range for the combox, I tried "=INDIRECT(IF(C1=1,C2,C3))", but only got an empty list. Is this 'illegal'? Thanks, Iyue Debra Dalgleish shows how to do this with Data|Validation. http://www.contextures.com/xlDataVal02.html Maybe you could use her technique on your comboboxes. If no, then post back with a little more info... Comboboxes from the ...

How Do I Copy A List Of Filenames Into A Word Document?
I have a list of filenames in a directory and I wish to copy and paste the names of the files only into a WORD document. How do I go about this task? See the freeware Printfolders utility that you can download from my web site. -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> &q...

"One or more of the selected senders could not be added to your blocked senders list"
Hey all. I get this when ever I try to killfile anyone at all, regardless of who they are. I'm on OE6 in XP Home and this has only just started happening. My KF only has a few spammers in it so I don't think it's full. Does anyone know what's going on? Thanks Ayatollah of rock 'n' roller wrote: > Hey all. I get this when ever I try to killfile anyone at all, regardless of > who they are. I'm on OE6 in XP Home and this has only just started > happening. My KF only has a few spammers in it so I don't think it's full. > Does anyone...