"The" to ", The"

[James]

Hi,
i have a list and i would like to change a cell to read "Goonies, The"
instead of "The Goonies".
any ideas?

 

[Chip Pearson]

James,

Assuming the value to be changed is in A1, use the following
formula

=IF(LEFT(A1,4)="The ",MID(A1,5,LEN(A1))&", The",A1)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[JE McGimpsey]

One way:

=MID(A1,FIND(" ",A1)+1,255) & ", " & LEFT(A1, FIND(" ",A1))

 

[Guest]

Press Ctrl+H, Then, for Find what : The Goonies
and Replace with : Goonies, The.

 

[James]

thanks guys!
this formula did exactly what i was needing!

 

[Guest]

-----Original Message-----
Hi,
i have a list and i would like to change a cell to read "Goonies, The"
instead of "The Goonies".
any ideas?

Ctrl+H
Find What = "The "
Replace What = ", The"

 

[Chip Pearson]

Did you try this? It won't work.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[James]

no, this wouldn't work for me because i fave a lot of different titles and
i'd have to Ctrl+H for each title.
the actual formula's worked like a charm.