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
1153 Views

Similar Articles

[PageSpeed] 49

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:

Count down to Words / Text
Hello, Is there a way to have a cell that is counting down from "10" and when it hits "0" spell the word FULL and in Red? example: cell BB10 has a formula in it: =COUNTA(H10:AV10) cell BB8 has a formula in it: =SUM(10-BB10) NOTE: there are only 10 entities max in cells H10 through AV10 and that is why I subtract BB10 from 10. I want cell BB8 to reflect the word ALL when there is 10 and FULL when the countdown hits "0" Can this be done? Thanks, Champ Try this....... =IF(SUM(10-BB10)=10,"ALL",IF(SUM(10-BB10)=0,&qu...

Word/Outlook 2002 email merge as plain text
I am trying to do an e-mail merge with Word and Outlook 2002 (all service patches installed). I use the mail merge wizard in Word and select recipients from Outlook through the merge wizard. When I choose plain text, the e-mail sends with nothing in the body of the e-mail and attaches the Word document. HTML works fine. I need to send the e-mails as plain text because the news outlets on my media list prefer plain text or the e-mail gets caught as spam. Any suggestions to get the messages to go as plain text? This used to work and now it doesn't! ...

Command Button to Sort and Filter by Selection
Is it possible to create a command button on a form that will allow you to sort records or to use the Filter by Selction option? They are not choices I can select while using the wizard or the macro builder. However, both of these would be extremely useful as I'm building a database for someone who has never used a database before and isn't all that comfortable with computers in general. I am using Access 2003 and I am NOT familiar with Access Visual Basic, Java or building macros from scratch. I'm sure there is a way to use these tools to create what I want, ...

sorting numbers #4
i have a simple sheet that basically consists of name, ip address, and then other columns obviously when i sort by ip address the .1,.2,.3,.4,.5, etc get sorted in between the higher numbers ie: 99 100 1 101 102 and i have to manually move them i cant seem to see how you can sort within a column if the above makes sense :) -- adetorry ------------------------------------------------------------------------ adetorry's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29151 View this thread: http://www.excelforum.com/showthread.php?threadid=488723 See: http...

Text box in a Word template
I want to insert a text box from the forms toolbar into a template (.dot) file. When I do this, I unprotect my document and add the text field. Then I protect the document again. This is OK execept there is nothing to indicate to the user what to type into that field; it is just blank until the user clicks there. I would like to provide the user with some indication of what to type in the field-- for example [enter date here]. Once he type there the message should get replaced with what he types. Any ideas? Thanks, Stanley You can type default text into a text form field. Dou...

insserting formatted word files into publisher
i'm using office 2003 and i'm making a book. i have fully formatted pages, each an individual file, saved in word. they have drop caps, specific margins, headings, etc. when i try to insert a text file by first creating a text box the full page size, the file i import comes over completely wrong. if i don't first make a text box the insert file choice is grayed out. i have 150 pages that need precision. i hope you can help. thanks. You will be far better off leaving your project in Word. You will not be able to maintain the formatting. Publisher does not support headers or ...

creating a certain chart from a certain dataset
Hi. I have some data about failures. the log file is like this (but really much longer): app1 03/04/2006 08:51 app2 03/04/2006 09:42 app1 03/04/2006 18:33 app3 03/05/2006 00:14 app1 03/05/2006 00:21 app2 03/05/2006 01:52 app1 03/06/2006 02:51 app3 03/06/2006 08:54 I would like to create some charts but don't know how. ex: app 3/4 3/5 3/6 app1 x x x app2 x app3 x ========================================= Where there is a dot or something to indicate the occurrence, and app1 is one row, app2 another row etc. can someone point me in...

Calendar control in Word 2003
I'm making a form which uses the calendar control 11.0 in Word 2003. I've managed to do so using: www. fontstuff.com/word/wordtut03a.htm This works fine on my form. However, the form is to be used at work, on a network (some staff use Citrix, some use PC's). I had to install the calendar control on my PC as described in the link. Only when I am logged on is the calendar useable- I'm assuming other users may have to have it installed, but the trouble is that we are a small department withing a large sector, so, is there any way around this as I doubt my chances at getting the c...

Calculate age as of a date certain
Employee benefit enrollment requires the age of each employee as of a specific date, such as 1/1/2005. Given the date of birth, how would this be calculated? -- Joe S. Hi see: http://www.cpearson.com/excel/datedif.htm#Age -- Regards Frank Kabel Frankfurt, Germany "Joe S." <JoeS@discussions.microsoft.com> schrieb im Newsbeitrag news:0EC4F9E5-2E60-448A-A107-7B085BC764C5@microsoft.com... > Employee benefit enrollment requires the age of each employee as of a > specific date, such as 1/1/2005. Given the date of birth, how would this be > calculated? > -- > Joe ...

How convert Numeric number in words
Is there any easy formula or way to convert numeric values in words? For example in cell A1 there is an amount 19549. How could it will be automaticaly converted by a formula in cell B1 as Ninteen thousand five hundred and forty-nine. Please give me an easy answer because i am not a programmer but just an excel user. Imran Murtaza There is no direct function to convert this. For a VBA solution check out the below links http://www.ozgrid.com/VBA/ValueToWords.htm http://support.microsoft.com/kb/213360 http://www.xldynamic.com/source/xld.xlFAQ0004.html -- Jacob "...

number of lines on a word document
Operating System: Mac OS X 10.5 (Leopard) I want to produce a long, double-spaced document with the same number of lines on each page, not one that flips to a new page if the end paragraph is too large to fit. Can anyone help? Maintaining a constant number of lines per page is dependent on at least 2 factors: 1- Using the same font & font size throughout, & 2- Turning off Widow/Orphan control in Format> Paragraph - Line & Page Breaks. Due to the proportional spacing considerations of modern word processing -- which is more closely akin to typesetting than typewr...

Populate cell with a value if another cell has a certain value
All, good morning. i have a issue, i need to populate cell E4 with a value CX/025966 when cell F4 has a text Bond Street. Is there a simple way of doin this? EXCEL 2007 Try:- =IF((F4="Bond Street"),"CX/025966","") If my comments have helped please hit Yes. Thanks. "B2ORL" wrote: > All, good morning. i have a issue, i need to populate cell E4 with a value > CX/025966 when cell F4 has a text Bond Street. > > Is there a simple way of doin this? Use IF() =IF(F4="Bond Street","CX/02...

Counting number of words in a sentence and highlighting or commenting
Hi, I found some old code, from Helmut of Bavaria I believe, that is supposed to find long sentences in a document: Sub test002() Dim rDcm As Range ' the documents main story range Dim oWrd As Object ' a word Dim oSnt As Object ' a sentence Dim oPrg As Paragraph ' a paragraph Dim lWrd As Long ' a counter for words Dim lSnt As Long ' a counter for sentences Dim lPrg As Long ' a counter for paragraphs Set rDcm = ActiveDocument.Range For Each oPrg In rDcm.Paragraphs lPrg = lPrg + 1 lSnt = 0 For Each oSnt I...

using addrress books to sort incoming mail
I use address books to sort incoming mail. An odd thing: depending on how the sender's name comes across, it doesn't get caught by the filter (amd moved to the "Family Mail" Folder I have set up. for example: my mom and dad's email (from momanddad@somedomain.com) comes as from that address. However, in my contacts, they are listed by their names: John and Mary Smith, with momanddad@somedomain.com as their email address and John and Mary Smith as the display name. What have I done wrong? TIA 1. The big ONE - failed to post your version of Outlook. Rules operate at ...

What "converter" to copy Word to Publisher?
My dad's tryign to copy a Word file into Publisher, but he's getting an error saying he needs a "converter" Anyone know what t his is,and where to get it? (if it's a download, EXACTLY what is it called? thx! After managing to set up OE-QuoteFix on his new PC, Ed reads a message from dgrrr <rewtheworld@msn.com>... > My dad's tryign to copy a Word file into Publisher, but > he's getting an error saying he needs a "converter" What version of Publisher, Word, and Windows is he using? What steps is he taking to try and put the Word file into...

create a macro that finds certain words in doc then pste
i am looking to create a macro that finds certain words in a word doc then cuts and pastes them into an ecxell spreadsheet. What have you come up with so far? ted wrote: >i am looking to create a macro that finds certain words in a word doc then >cuts and pastes them into an ecxell spreadsheet. -- Gerry Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/word-programming/201003/1 ...

Linking many EXCEL tables into a long WORD document
Would anyone have direct experience with linking lots of Excel tables into complicated Word reports - say 100 tables into a 100 page document? I tried this years ago shortly after OLE came out, but the document's links got confused, with Excel occasionally getting confused about which worksheet tab and range belonged in which Word table. This was not a case of my error, nor of corrupted files due to crashes, but was confirmed as a problem with Excel by a person knowledgable of its codes and bugs back then. Needless to say, I stopped aiming at having these live links in my docum...

Document created by older version of Word won't open
I'm currently using the version of Word contained in Office 2003. I have some documents I created with earlier versions which no longer can be opened. I get this error message: "You are attempting to open a file that was created in an earlier version of Microsoft Office. This file type is blocked from opening in this version by your registry policy setting." How do I fix this? Thanks! John -- Please reply in this newsgroup. I never post my true email address to prevent spam. Thank you. See "Information about certain file types that are blocked after yo...

Word document attachment can not be read
We are using Office XP SP3 and Exchange 2000 with SP3. Some of our users will send Word documents as attachments in an email from Outlook that when you open the attachment, word goes into file conversion because it can't read the document. Typically, they send it again and it opens fine. Sometimes it has taken two or three tries. It is random and inconsistent. I have not been able to duplicate it. Has anyone else experienced this? Thanks. ...

sorting in excel #5
i have been working on a spread sheet of data for about 5 months. today i am trying to change some of the formating as update entries. in the past i would sort the data in this worksheet using the sort option under the data menu. Suddenly today the data menu only has the options, "Filter, Group and Outline, and Import External Data." The rest of the options have been ghosted and do not work. I tried copying and pasting into a new document. But as soon as i do that the document no longer has the option of sorting. Has some setting been changed? What do i do? Hi is your ...

Sort Treats Empty Cells As Largest Value??
I have several projects that sort data from greatest to least (descending). That is, nothing is considered greater than a million. Of about 4000 rows between a third and a half are blank (no count). I need the maximum values/count at the top of the list, but Excel puts these empty cells at the top of the list. As a result I have to do a lot of moving of large groups of data around to put the empty/blank cells at the bottom of these lists -- a real time consuming pain. Is there a way to have Excel treat empty/blank cells as having lower values than cells with values? I assume I could f...

Cannot open certain Files.
For some reson or another I cannot open one of our newletter issues. The file will open if publisher runs its self in safe mode.....Im baffled? any ideas ? Graham Could be the printer driver. Have you changed printers or the driver? When you open the file in safe mode, save as...something else, see if it will open then. -- Mary Sauer MS MVP http://office.microsoft.com/ http://www.msauer.mvps.org/ news://msnews.microsoft.com "JGM" <marxymarks@hotmail.com> wrote in message news:eJeXwuzxEHA.2212@TK2MSFTNGP15.phx.gbl... > For some reson or another I cannot open one ...

Excel and Word 2007
Is it possible to switch back to the 2003 display with pull down menus in lieu of all the new Icons? If so how? I find the 2007 versions very difficult to use. You can get (some are free) addins Classic Menu and Toolbars in XL 2007 http://www.addintools.com/english/menuoffice/ Excel-2007 add-in to display XL 2003 menubars & toolbars http://in.geocities.com/shahshaileshs/addins.htm ToolbarToggle: http://www.toolbartoggle.com/ But if you give it a decent chance you will get used to the ribbon best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme ...

Word #2
When i type a document, it only prints every other line. I have succesfull y printed in the fast. Do you mean every other line appears on the screen? Do you mean the printer prints every other line? Do you mean it creates a double spaced document? Let's have another go at it chap. -- Don Vancouver, USA "flonge" <flonge@discussions.microsoft.com> wrote in message news:E987A317-0D2A-454A-9576-50E0C2E8AAA6@microsoft.com... > When i type a document, it only prints every other line. I have succesfull y > printed in the fast. flonge wrote: > When i type a docu...

Sort associated entity attribute columns in a view
Hi folks, Has anyone found a solution to this possible bug? It's not resolved in RU2. Suppose you have a relationship between two entities, Household and Family. In the customizations area of the Household entity, on the active view, you specify columns from both the household entity (household name, city) and associated Family entity (sibling name, telephone number). When you publish the Household entity, and view the active households, you see a list of records, and the columns 'household name', 'city', 'sibling name', and 'telephone number'. You...