How do I move words around in a cell automatically?

**pmickey** · 10-14-2009, 05:38 PM

I have a very long list of music files and I want to sort bands by name. However, with bands like "The Beatles," I would like to sort these as "Beatles, The."

Is there a formula that will automatically do this?

Thanks!

**shg** · 10-14-2009, 05:53 PM

No. You'd have to create a separate sort column with the articles moved around as you like.

**Special-K** · 10-14-2009, 10:04 PM

In the single example you gave this formula would produce the required result

=IF(ISERROR(FIND("X",A7))=TRUE,RIGHT(A7,LEN(A7)-FIND(" ",A7))&", "&LEFT(A7,FIND(" ",A7)-1),A7)

and would work for any cell that contains 2 names seperated by a space