Sort and exclude certain words

Hi ...

I work in a public library and frequently export lists of books from
our online catalog to Excel, so that I can manipulate the data in
various ways.

My problem in sorting in Excel is this:  Our library catalog excludes
words like "The" "A" or "An" at the beginning of a title when sorting. 
Excel, however, does not.

Is there any way I can sort on a field in Excel and have it ignore
certain words at the beginning of a string?

Thanks!


-- 
jds217
------------------------------------------------------------------------
jds217's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28608
View this thread: http://www.excelforum.com/showthread.php?threadid=482709

0
11/7/2005 3:57:40 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
1159 Views

Similar Articles

[PageSpeed] 11

One way:

If your data is in column A starting in row 2 then in any unused column 
in row 2 enter the formula:
=IF(LEFT(A2,4)="The ",MID(A2,5,256),
IF(LEFT(A2,2)="A ",MID(A2,3,256),
IF(LEFT(A2,3)="An ",MID(A2,4,256),A2)))

Copy this down as far as needed and then select all your data and sort 
on this new column.

Hope this helps
Rowan

jds217 wrote:
> Hi ...
> 
> I work in a public library and frequently export lists of books from
> our online catalog to Excel, so that I can manipulate the data in
> various ways.
> 
> My problem in sorting in Excel is this:  Our library catalog excludes
> words like "The" "A" or "An" at the beginning of a title when sorting. 
> Excel, however, does not.
> 
> Is there any way I can sort on a field in Excel and have it ignore
> certain words at the beginning of a string?
> 
> Thanks!
> 
> 
0
11/7/2005 4:19:17 AM
On Sun, 6 Nov 2005 21:57:40 -0600, jds217
<jds217.1y3yty_1131336001.6988@excelforum-nospam.com> wrote:

>
>Hi ...
>
>I work in a public library and frequently export lists of books from
>our online catalog to Excel, so that I can manipulate the data in
>various ways.
>
>My problem in sorting in Excel is this:  Our library catalog excludes
>words like "The" "A" or "An" at the beginning of a title when sorting. 
>Excel, however, does not.
>
>Is there any way I can sort on a field in Excel and have it ignore
>certain words at the beginning of a string?
>
>Thanks!

I wonder about your card catalog.  For example, is the book "A Tree Grows in
Brooklyn" cataloged under the "A's" or the "T's"?  (or both).

In any event, there are various methods, all of which involve constructing a
separate list that excludes the initial word, and sorting on this new list.

If there are just a few words to be excluded, and not much change in the list,
then a simple, nested IF function as recommended by Rowan will work just fine.

If there are a larger number of words, then a more general solution could be
had by using the following formula to construct your "word-stripped" list.

=IF(COUNTIF(ListOfWords,LEFT(A1,FIND(" ",A1)-1)),
TRIM(MID(A1,FIND(" ",A1)+1,255)),A1)

ListOfWords is a range where you have listed all of the first words which you
want excluded.  You may either NAME the range, or substitute the reference in
the formula (e.g. Z1:Z10)

VBA solutions are also available.


--ron
0
ronrosenfeld (3122)
11/7/2005 12:39:05 PM
Reply:

Similar Artilces:

Printing protected Form in Word Mac 2008 & Office Documentation
I received a form to be filled; filling the form is no a problem. I can see on the screen AND in preview mode the full form and its filled content. However, as soon as I want to print it I just get the template form with statements as {FORMTEXT} statements and so on.... Saving or printing as .pdf is he same !!!! Unfortunately, I have lost my original CD (assume that the documentation was in there) I just have my tiny yellow box with its barcodes and my invoice...... Is there a .pdf doc set of office 2008 avail somewhere ? I'm lost and there is nothing related in the OnLi...

multiply a row by a certain number?
i am having trouble with excel i want to multiply this row by 1.4 and make it appear next to it if anyone can help thank you if you mean multiply each item in a column then this will work. Modify to suit Sub multiplyall() For Each c In Range("c7:c10") c.Offset(, 1) = c * 1.4 Next End Sub -- Don Guillett SalesAid Software donaldb@281.com "multiply a row by a certain number?" <multiply a row by a certain number?@discussions.microsoft.com> wrote in message news:C84A6F67-03CD-4902-9760-36051A179831@microsoft.com... > i am having trouble with excel i want to multipl...

Make Word Bigger than Screen Size
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I have a Macbook and like to work on a word document using the &quot;two pages&quot; view. This is great except that I'm loosing an inch on either side of the screen made up of gray space and margins. <br><br>I'd like to be able to make the window bigger than the screen. Then I could park the margins and the gray space off screen and use all of my screen by enlarging my view of the documents without being forced to go back to viewing one page. <br><br>Currently Word wo...

Access to Word 02-28-08
Know this is real general...I work a lot in Access. Want quotes created with word due to unstructured nature...there any good documents that would give me some vision on using data from access to open a template in word that might have address detail populated and some other static entries? Thanks. Maybe this will help: http://en.allexperts.com/q/Using-MS-Access-1440/report-Output-word-template.htm "Tony Ramirez" <TonyRamirez@discussions.microsoft.com> wrote in message news:AFFF9F82-4D16-4485-9AE0-A5A494B2B556@microsoft.com... > Know this is real general...I work a l...

Prevent Word from creating a new open document every time I click on its icon in the dock?
I've got Word 2008, and when there's no document open, and you switch to Word from another app (by clicking on its icon in the dock), it immediately opens (creates) a new blank document. Any way to get rid of that behavior? Mike Hi Mike: Have a stern word with Steve Jobs :-) That's a required behaviour for a compliant Apple Macintosh application. Activate from the Dock must result in "somewhere to accept input" (i.e. A new blank document). You will have a fight on your hands, most users prefer it that way. If you use Command + Tab to switch to Word,...

nonsense words
where can i download those nonsense words or fake text to fill up a text box for example. help and thanks. Google for "lorem ipsum" and you'll get plenty to choose from. -- Hugs, Erika Just go to the Symantec website and download the text from any pages as it is all lies, garbage, false claims etc. etc. and just a lot of nonsense sales department meaningless words. -- ...

word 07 won't change the open file to the name I just "saved as"
I am new to WOrd 07, but am familiar with 97. If I open a file in and old format and then save it using "save as" and even save it to the new format, why doesn't it change the name of the file I am working on to the new file name? I also get a second word window with the original file opened. Part of the "Save as" is to change the name. You must do this yourself as no one else has the foggiest notion what your desire. "rgw1085" <rgw1085@discussions.microsoft.com> wrote in message news:121620EB-B1B6-4182-B9AE-DFD68BAA5879@microsoft.com... ...

Graph with horizontal color blocks marking certain numbers
I'm doing up a graph to plot Body Mass Index. Easy calculation based on weight and height, and will plot it at one month intervals as part of a weight loss charting method. As you probably know, BMI is broken down into certain cetagories (ie 0-18.4 (underweight)), 18.5-24.9 (normal), 25-29.9 (overweight), over 30 (obese). I'd like each of these categories (ie horizontal zones) a different color, and then on top of this "background" plot the actual values. One can then look at the change over a weight loss program. I plan of having BMI as the y axis and various date...

How do I sort inbox on two columns
A person can sort the inbox by (for example) clicking on the top of the date colun. After a few seconds the list is sorted by date. Click on the from column and after a few seconds the list is sorted by "from" but what if I would llike to sort by from within date or date within from, or any other combiation of two columns. How can I do that? K View-> Arrange By-> Custom...-> Group By: From-> Sort: received -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, A...

Word font color keyboard shortcut
I'm requesting that a keyboard shortcut for applying the currently selected font color be designed into Word. I suggest Alt+C. It's very easy to highlight the desired text with the mouse in the right hand, and use two left hand fingers to apply the font color by shortcut. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-ba...

Simple Count of certain cells
Whats the simplest way of counting how many times certain letters appear in a list? In cells A1:A2000, I have Ethnicity codes with singular letters A-Z, I would like to count cetrain groups of letters say A,C,G,P,Z. Thanks =COUNTIF(A1:A2000,"A") -- David Biddulph "JPDS" <JPDS@discussions.microsoft.com> wrote in message news:B782FA55-E4AD-4680-B426-10FCF2E0D3FD@microsoft.com... > Whats the simplest way of counting how many times certain letters appear > in a > list? > In cells A1:A2000, I have Ethnicity codes with singular letters A-Z,...

Unable to receive email from certain senders
I have been succesfully running Exchange 2003 for approx 4 months and as of today some of my users are reporting that they are either not getting emails or sporadically receiving emails. My current test shows this: I can successfully send from my yahoo hosted POP3 acct from their web interface and Hotmail to their accounts every time. I can't send from this same acct via Outlook which is using my local ISP's outgoing SMTP server. My Outlook settings are: SMTP smtp.west.cox.net Local ISP POP3 pop.bizmail.yahoo.com Yahoo's Biz email servers The reply I get ...

external data table sort order
Hi, I am querying a SQL database view to return a table of data in Excel. The view is setup to sort the data they way I need it so my query within excel is literally "select * from MyView". However in Excel 2007 I now get this fancy data table with filter headers that seem to override my views sort order. If I use the filter to sort a column then this is the dominent sort order even if I remove the filter from the table and refresh the data. So how do I retrieve external data in the order it was intended in? Thanks, Matt -- Thanks! ...

How to control Word from Excel?
Hi I want to create some code in VBA. I fill out an Excel Table an after that I start my code. I create (from Excel) a Word Document an filled it out with some of the the content out of the Excel Table. This works fine. But now I want to format some paragraphs in another Fontsize or align a paragraph on the right side. But how can I do that? I tried this... Example: appWord.Application.ActiveDocument.Content.InsertAfter text:="Size 10" & Chr$(13) appWord.Application.ActiveDokument. *???* .Font.Size = 8 appWord.Application.ActiveDocument.Content.InsertAfter text:="Size 8&...

PDF Files created from Word 2007
If I save a file in PDF format and send it to someone as a n attachment, will they be able to modify it if they have adobe software. If so how do I make it so they can't change the original document thanks BV On Mar 5, 6:48=A0pm, Techtrainer <Techtrai...@discussions.microsoft.com> wrote: > If I save a file in PDF format and send it to someone as a n attachment, = will > they be able to modify it if they have adobe software. If so how do I mak= e it > so they can't change the original document > thanks > BV One way to prevent editing is to print o...

Sorting and Grouping Problems in Outlook 2003
First, let me say that I'm incredibly confused by the new methodology that Microsoft has implemented in Outlook 2003 to handle sorting, grouping, views, etc. I wish they would have left it the way it was in Outlook 2002, as it was relatively simple and easy to understand, as well as easy to accomplish what you wanted. From what I can understand, these new sorting and grouping methods, called Arrangements, seem to control alot of how sorting and grouping is done. However, at times, I like to be able to sort and group independently of each other, which it seems very hard to do. The way my...

Adding "button" to click to sort updated data?
I'm using Excel 2003 and need to add a button that users can click to sort new data. Once a row has been updated with new data, I want the users to simply be able to click this "button" in order to sort the new data in descending order. I have several different sets of data that I want to add these "buttons" to. One for example is found between rows R5-R16. I have results from each row's data in column R. I then users to be able to simply click this new "button" to sort those results. How can I do this? Thanks in advance for any help !!! Turn...

Visio to Word to Outlook
I tried this question about a month ago, and got no helpful replies; perhaps I wasn't clear enough. I have Word documents that are responses to students. I'd like to prepare drawings in Visio - insert them into the Word document (which works fine), and then cut and paste sections of the Word document (including the drawing(s) into an Outlook (for the Web) mail message. I've tried to save/export the Visio drawing in various formats, but in no case does the drawing appear in the Outlook mail message. I'm using XP-home, Word 2000, Visio 2003 (which I purchased for this project)...

how to use the function sorting in excel
can you help me on how to use the function sorting in excel. thank you "excel2003" wrote: > can you help me on how to use the function sorting in excel For a good start, suggest you try Excel's help, Click Help>Microsoft Excel Help, search for: sorting The fundamentals are all there. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- Data>Sort then accomadate your data accordinly. Or Data>Filter>Autofilter in order to use dropdown list to sort. -- Can''''t hear the Rain ? Then listen to it. "excel2003" wrote: &...

Changing 1 word to another word on a different page
I have a work sheet that if an answer in a cell on page 2 is "Yes" or "No", I need it to display the opposite in a cell on page 1 In a1 in sheet1 put =IF(Sheet2!A1="yes","no","") -- Russell Dawson Excel student "Circuitman57" wrote: > I have a work sheet that if an answer in a cell on page 2 is "Yes" or "No", > I need it to display the opposite in a cell on page 1 Missed a bit =IF(Sheet2!A1="yes","no",IF(Sheet2!A1="no","yes","")) -- ...

Word 2008 in Snow leopard
2004 seems a bit sluggish at times. Is 2008 much faster on a Intel Mac? John -- Are there errors in the Bible? Do Animals go to Heaven? Is drinking Alcohol a sin? Is the KJV the only translation to be used? If you want to learn, get answers, and be able to defend the faith, CERM is your place. http://www.cerm.info/ John Wolf wrote: > 2004 seems a bit sluggish at times. Is 2008 much faster on a Intel Mac? > > > John Hi John, It depends upon what you are working on. Can you describe the circumstances in which Word 2004 is sluggish? Really long document? L...

Linking Word data to a Excel spreadsheet
New Word user here. Question: Is it possible to link data that is entered into a "fillable form" field, so that the identical data is ALSO entered simultaneously into a specified Excel cell? Hi MBR, Yes, via vba (The macro language Word & Excel use). However, it's not something a new user is likely to find easy to set up, unless already fairly well experienced with vba. -- Cheers macropod [Microsoft MVP - Word] "MBR" <MBR@discussions.microsoft.com> wrote in message news:CF3ED231-5FCA-4F2B-B651-FA5B6276A4F7@microsoft.com... > New Wor...

Word 2007 crashes on opening
recently MS Word frequently crashes as soon as the app is opened, and only by shutting down Windows and restarting am I able to get the program stable. The error dialogue is as follows: Fault Module Name: StackHash_3c89 See http://word.mvps.org/FAQs/AppErrors/ProblemsStartingWord.htm. -- Stefan Blom Microsoft Word MVP "ajaxt" <ajaxt@discussions.microsoft.com> wrote in message news:78256ACC-7DBF-4AD9-90C6-767888A8D8A8@microsoft.com... > recently MS Word frequently crashes as soon as the app is opened, and only by > shutting down Windows and restart...

Cannot open attached Word documents from Outlook
I get the no file association error when trying to open a Word document that is attached to an email. I have checked and the association is there, I can open them form Windows Explorer. Does any one have any ideas Using Outlook 2000 (not sure of service poack level) CW Mode on Windows 2000 Pro SP4. TIA ...

Repeating (or Looping?) an Action with VBA in Word 2007
Hello, I am very new to VBA. I am trying to create a macro for a Word 2007 document that finds a graphic, then resizes it to original size (Reset in Format Picture dialog box). I have recorded the following, which produces the result I want for one graphic. How do I make it loop so that it will perform the action on ALL the graphics in my document? When responding, please keep in mind that I am a novice with VBA. :) Sub ResizeSlide() ' ' ResizeSlide Macro ' ' Selection.Find.ClearFormatting With Selection.Find .Text = "^g" ...