Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
happy1
 
Posts: n/a
Default How can I sort text (in excel) while ignoring articles (e.g., the

I work in a library and want to sort lists of CD titles. I want to sort the
list alphabetically while ignoring articles (e.g., the, a, an) when they
appear as the first word in the title.

  #2   Report Post  
Posted to microsoft.public.excel.newusers
Nicky
 
Posts: n/a
Default How can I sort text (in excel) while ignoring articles (e.g., the


Hi
I don't think you can do this.
what might work is to include the names in an adjacent column and
eliminate the articles, then sort on that.
For example, this formula will eliminate the words "The ", "An " and "A
" from the left of the text in cell A1 (note the addition of a space
after the word in the formula, to prevent all words begining with "A"
being cropped):

=IF(LEFT(A1,2)="A ",RIGHT(A1,LEN(A1)-2),IF(LEFT(A1,3)="An
",RIGHT(A1,LEN(A1)-3),IF(LEFT(A1,4)="The ",RIGHT(A1,LEN(A1)-4),A1)))


--
Nicky


------------------------------------------------------------------------
Nicky's Profile: http://www.excelforum.com/member.php...nfo&userid=312
View this thread: http://www.excelforum.com/showthread...hreadid=518525

  #3   Report Post  
Posted to microsoft.public.excel.newusers
Jim Cone
 
Posts: n/a
Default How can I sort text (in excel) while ignoring articles (e.g., the

A commercial application by yours truly...
http://www.officeletter.com/blink/specialsort.html
Jim Cone
San Francisco, USA


"happy1"
wrote in message...
I work in a library and want to sort lists of CD titles. I want to sort the
list alphabetically while ignoring articles (e.g., the, a, an) when they
appear as the first word in the title.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sort order : Excel vs imported data John Excel Discussion (Misc queries) 0 December 9th 05 02:40 PM
How can I ignore articles in a sort using Excel or Word? Nothappy Excel Discussion (Misc queries) 2 December 2nd 05 05:23 AM
Import text to Excel Fredrik E. Nilsen Excel Discussion (Misc queries) 2 November 21st 05 07:55 PM
Is there a way to sort text by frequency within Excel? jellis1919 Excel Discussion (Misc queries) 1 August 12th 05 01:40 AM
Can you sort text in a random order in Excel tbowden Excel Discussion (Misc queries) 3 February 1st 05 03:42 PM


All times are GMT +1. The time now is 04:15 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"