change "Expositor, The" to "The Expositor"

[gerry.lisa]

The is nothing else in the cell, I have about 100 of these to do.
Thanks for your help!

....Lisa

XL 2003

 

[Guest]

It's a bit long winded so I hope someone comes up with something better but
in the meantime with your word in A1 put this in B1.

=RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1,"
","")))))&" "&IF(ISERR(FIND(" ",A1)),A1,LEFT(A1,FIND(" ",A1)-1))

Mike

 

[Guest]

you could just insert a column and put this formula in and copy it down.
(Assuming that your 1st one starts in A1) Then copy that column and 'paste
special'...'values' on top of the original column, then delete the column you
created with the formulas.

=RIGHT(A1,3)&" "&LEFT(A1,9)

 

[Guest]

Lisa: Perhaps I am approaching this too simplistically, but have you tried
Find and Replace (Ctrl + H) to change all occurences of "Expositor, The" to
the desired "The Expositor"?

 

[gerry.lisa]

Thank you but tat was only 1 example of 100...I had many newspaper
names with the "The" after the paper's name.

....Lisa

 

[Guest]

Do the following:

Set the AutoCorrect options
1. On the Tools menu, click AutoCorrect Options.
2. On the AutoCorrect tab, decide what types of corrections you want to
make. You can also specify exceptions to the selections you make by clicking
the Exceptions button.

Challa Prabhu