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

C

Carol in GA

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
 
D

Don Guillett

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
 
B

Bob Phillips

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

--
HTH

Bob Phillips

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

James Silverton

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
 
J

Jim Cone

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 [email protected]>
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
 
Top