"The Title of a Song" to "Title of a Song, The" possible?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Carol in GA - 30 Sep 2006 18:38 GMT

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

Reply to this Message

Don Guillett - 30 Sep 2006 19:05 GMT

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

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

> I'm creating a spreadsheet of my music collection and am having trouble
> with
[quoted text clipped - 8 lines]
> Thanks!
> Carol

Reply to this Message

Bob Phillips - 30 Sep 2006 19:22 GMT

=IF(LEFT(A1,3)="The",MID(A1,5,99)&", The",A1)

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> I'm creating a spreadsheet of my music collection and am having trouble with
> the way things are sorting.
[quoted text clipped - 7 lines]
> Thanks!
> Carol

Reply to this Message

James Silverton - 30 Sep 2006 19:28 GMT

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

Reply to this Message

Jim May - 30 Sep 2006 19:51 GMT

Another method:

=TRIM(SUBSTITUTE(A1,"The",""))

> Hello, Carol!
> You wrote on Sat, 30 Sep 2006 10:38:02 -0700:
[quoted text clipped - 16 lines]
> E-mail, with obvious alterations:
> not.jim.silverton.at.comcast.not

Reply to this Message

Jim Cone - 30 Sep 2006 19:33 GMT

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

Signature

Jim Cone
San Francisco, USA

"Carol in GA" <Carol in GA@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

Reply to this Message