"The Title of a Song" to "Title of a Song, The" possible? Carol in GA 30.09.2006 17:38:02 I'm creating a spreadsheet of my music collection and am having trouble with the way things are sorting. 1. Data was entered as: "The Allman Brothers Band" through importing data from another source. 2. I would prefer it to be sorted as "Allman Brothers Band, The" instead. Is there a way to format the text (not each entry and not through the replace functions) to display and sort in the prefered method? Thanks! Carol

Re: "The Title of a Song" to "Title of a Song, The" possible? "Don Guillett" <dguillett1[ at ]austin.rr.com> 30.09.2006 18:05:47 something like for each c in selection if left(c,3)="The" then c.value=right(c,len(c)-4)&"," & left(c,3) next c -- Don Guillett SalesAid Software dguillett1[ at ]austin.rr.com "Carol in GA" <Carol in GA[ at ]discussions.microsoft.com> wrote in message news:9C2286F1-9C25-4B70-96FD-30F242C3C5F5[ at ]microsoft.com... [Quoted Text] > I'm creating a spreadsheet of my music collection and am having trouble > with > the way things are sorting. > 1. Data was entered as: "The Allman Brothers Band" through importing data > from another source. > 2. I would prefer it to be sorted as "Allman Brothers Band, The" instead. > > Is there a way to format the text (not each entry and not through the > replace functions) to display and sort in the prefered method? > > Thanks! > Carol

Re: "The Title of a Song" to "Title of a Song, The" possible? "Bob Phillips" <bob.NGs[ at ]somewhere.com> 30.09.2006 18:22:37 =IF(LEFT(A1,3)="The",MID(A1,5,99)&", The",A1) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Carol in GA" <Carol in GA[ at ]discussions.microsoft.com> wrote in message news:9C2286F1-9C25-4B70-96FD-30F242C3C5F5[ at ]microsoft.com... [Quoted Text] > I'm creating a spreadsheet of my music collection and am having trouble with > the way things are sorting. > 1. Data was entered as: "The Allman Brothers Band" through importing data > from another source. > 2. I would prefer it to be sorted as "Allman Brothers Band, The" instead. > > Is there a way to format the text (not each entry and not through the > replace functions) to display and sort in the prefered method? > > Thanks! > Carol

Re: "The Title of a Song" to "Title of a Song, The" possible? "James Silverton" <not.jim.silverton[ at ]comcast.not> 30.09.2006 18:28:22 Hello, Carol! You wrote on Sat, 30 Sep 2006 10:38:02 -0700: CiG> Is there a way to format the text (not each entry and not CiG> through the replace functions) to display and sort in the CiG> prefered method? I think you would need a helper column using IF and the MID functions, something like: =IF( (MID(A1,1,4) = "The ") , (MID(A1(5,30) & ", The"), A1) since, it seems to me that you *are* replacing a subset of your titles before sorting. James Silverton Potomac, Maryland E-mail, with obvious alterations: not.jim.silverton.at.comcast.not

Re: "The Title of a Song" to "Title of a Song, The" possible? "Jim Cone" <jim.coneXXX[ at ]rcn.comXXX> 30.09.2006 18:33:57 Couple of ways to do it using formulas here... http://snipurl.com/xogu They handle more than just "the". A commercial solution can be found here (blush) ... http://www.officeletter.com/blink/specialsort.html -- Jim Cone San Francisco, USA "Carol in GA" <Carol in GA[ at ]discussions.microsoft.com> wrote in message I'm creating a spreadsheet of my music collection and am having trouble with the way things are sorting. 1. Data was entered as: "The Allman Brothers Band" through importing data from another source. 2. I would prefer it to be sorted as "Allman Brothers Band, The" instead. Is there a way to format the text (not each entry and not through the replace functions) to display and sort in the prefered method? Thanks! Carol