Cell searching and Replacing
 

Hi Could use some help here please have a movie list
someone sent to me and it has cells like
Age of Innocence, The
Bone Snatcher, The
Etc Etc
I would like to do a Next procedure and Replace the text
, The
and put the The in Front of the text
So it is like this
The Age Of Innocence
The Bone Snatcher
Etc Etc
Hope someone can be kind enough to help me
Regards
Graham

Try

=IF(RIGHT(A1,5)=", The",RIGHT(A1,3)&" "&LEFT(A1,LEN(A1)-5),A1)


Hope it helps.


Graham Feeley Wrote:
Hi Could use some help here please have a movie list
someone sent to me and it has cells like
Age of Innocence, The
Bone Snatcher, The
Etc Etc
I would like to do a Next procedure and Replace the text
, The
and put the The in Front of the text
So it is like this
The Age Of Innocence
The Bone Snatcher
Etc Etc
Hope someone can be kind enough to help me
Regards
Graham


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=393313

Graham,

Assuming the movie names are in column A, how about this for column B:

=if( right(A1,5)=", The", "The " & left(A1, len(A1)-5), A1 )

Eric


"Graham Feeley" wrote:

Hi Could use some help here please have a movie list
someone sent to me and it has cells like
Age of Innocence, The
Bone Snatcher, The
Etc Etc
I would like to do a Next procedure and Replace the text
, The
and put the The in Front of the text
So it is like this
The Age Of Innocence
The Bone Snatcher
Etc Etc
Hope someone can be kind enough to help me
Regards
Graham

"Graham Feeley" wrote in message
u...
Hi Could use some help here please have a movie list
someone sent to me and it has cells like
Age of Innocence, The
Bone Snatcher, The
Etc Etc
I would like to do a Next procedure and Replace the text
, The
and put the The in Front of the text
So it is like this
The Age Of Innocence
The Bone Snatcher
Etc Etc
Hope someone can be kind enough to help me
Regards
Graham




Edit, Replace won't do it. There are some text functions that will do this,
probably in two steps. But, are you sure you want titles to retain "The" at
the beginning? Granted, you'll still be able to alphabetize the list, but
won't the titles be easier to spot without "The"? You can use Edit, Replace
to strip away the characters and replace them with nothing, but it won't
move the characters.